Shelly Cashman Access 2019 | Module 3: SAM Project 1b City Tours Ltd

Subject:MS AccessPrice:19.99 Bought7

Share With

Shelly Cashman Access 2019 | Module 3: SAM Project 1b

City Tours Ltd.

Maintaining a Database

GETTING STARTED

• Open the file SC_AC19_3b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_3b_FirstLastName_2.accdb by changing the “1” to a “2”.
  • If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. City Tours Ltd. needs to update the database on a regular basis. As a customer service manager for the company, you need to add new customers and confirmations, update basic costs, delete confirmations, and so on. In Query Design View, create an Update query based on the Tours table.
   1. Select the TourNumber and BasicCost fields from the Tours table.
   2. Add criteria to select only those records where the TourNumber field value equals W001.
   3. Update the BasicCost field with 24 as the new field value.
   4. Save the query using UpdateBasicCost as the name, run it, and then close it. The query should update one record.
2. Individuals who take one of the Segway tours need to have a short lesson and sign a liability waiver. In Query Design View, create a Make Table query that selects all the fields from the Confirmations table in the same order that they are listed in the field list. Select all the fields individually for the design grid; do not use the (*) asterisk.
   1. Add criteria to select only those records where the TourNumber field value is S001 or S002.
   2. In the Make Table dialog box, assign the name SegwayTours to the new table.
   3. Save the query using MakeSegwayTours as the name, run it, and then close the query. The new SegwayTours table should contain five records.
3. In Query Design View, create a Delete query for the Customers table by performing the following tasks:
   1. Select the FirstName field from the Customers table.
   2. Add criteria to delete only those records where the FirstName field values equal Siam.
   3. Save the query using DeleteSiamRecord as the name, run it, and then close it. The query should delete one record.
4. You need to add customers that reside in Vermontto the NewEngland table. In Query Design View, create an Append query for the Customers table.
   1. Select all the fields from the Customers table in the same order that they are listed in the field list. Select all the fields individually for the design grid; do not use the (*) asterisk.
   2. Add the criteria VT to the State field.
   3. Select NewEngland as the destination table in the Append dialog box.
   4. Save the query using AppendNewEngland as the name, run it, and then close it. The query should append one record.
5. Create a Split Form for the Confirmations table. Save the form, using Confirmations Split Form as the name.
6. Switch the Confirmations Split Form to Form view, navigate to the record with the ConfirmationID field value 1900119, and delete the record. Close the Confirmations Split Form.
7. Many tourists asked about the activity level of certain tours. For example, will there be a lot of walking. Open the Tours table in Design View and perform the following tasks:
   1. Move the ActivityLevel field so that it immediately follows the BasicCost field. Save the changes to the table design.
   2. Use the Lookup Wizard to change the ActivityLevel field to a Lookup field. Select the option to type in the values that you want.
   3. Enter the values shown in Table 1 in the order shown.
   4. Limit the values to only the items in the list, and do not allow multiple values for the field.
   5. Save the changes to the table design, but do not close the table.

Table 1: Values for ActivityLevel Lookup Field

 

Easy

Moderate

Strenuous

 

8. With the Tours table still open in Design View, delete the StartTime field. Save the change to the table.
9. Switch to Datasheet View and perform the following tasks:
   1. Add the caption Exercise to the ActivityLevel field.
   2. Change the ActivityLevel field value to Strenuous for Segway Tour2 (which has a TourNumber field value of S002). Close the Tours table.
10. Open the Groups table in Design View and perform the following tasks:
    1. Make the GroupID field the primary key.
    2. Change the field size property for the CustomerID field to 5.
    3. Change the data type for the Cost field to Currency.
    4. Save the changes to the table.
    5. Add a new field TotalCost after the Cost field. The field is a calculated field. Type the expression [GroupSize]*[Cost] to calculate the field values.
       
       Save the changes to the Groups table and then close it.
11. Open the Confirmations table in Design View and perform the following tasks:
    1. Change the Decimal Places property for the AdultCost field to 2.
    2. Change the Format property for the TourNumber field so that any letters in the field display in uppercase. (Hint: Use the > symbol.)
    3. Enter Number of Children as the Description for the NumChild field.
       
       Save the changes to the Confirmations table and then close it.
12. Open the Tours table in Datasheet View. Add a new field, to the end of the table (Click to Add column). The field is a calculated field with the currency format. Use the expression [BasicCost]*0.95 to calculate the field values. Assign the name DiscountedCost to the field. Save the changes to the table and close it.
13. Open the HomeTours table in Datasheet View and perform the following tasks:
    1. Resize the ConfirmationID, and TourNumber columns to display the complete field names.
    2. Remove the Total row from the table.
    3. Sort the records in ascending order by TourNumber.
       
       Save the changes to the HomeTours table and then close it.
14. Open the Guides table in Design View and make the following changes:
    1. Enter a Validation Rule for the HourlyRate field to ensure that values in the field are greater than or equal to 11.00.
    2. Enter the text, Must be greater than or equal to $11.00 in the Validation Text property box.
       
       Save the changes to the Guides table and then close it. (Hint: Because a validation rule was added, a warning message appears asking if you want to test the data. The data is valid, so ignore this message and continue saving the table.)
15. Open the Relationships window and add the Customers and Confirmations tables to the Relationships window. Create a one-to-many relationship between the CustomerID field in the Customers table and the CustomerID field in the Confirmations table. Make the relationship enforce referential integrity. Save the relationship.
16. Use the Find Unmatched Query Wizard to create a query to find all customers who do not have confirmations.
    1. Select the Customers table as the table to display in query results.
    2. Select the Confirmations table as the related table.
    3. Select the CustomerID as the common field in both tables.
    4. Display all fields in the query result.
    5. Save the query using UnmatchedConfirmations as the name.
       
       View the query results. There should be nine records. Close the query.
17. Open the TotalTourCost query in Design View. Use the Expression Builder to create a calculated field that calculates the result of multiplying the NumAdult field by the AdultCost fields. Assign the alias (caption)TotalTourCost to the calculated field. Open the query in Datasheet View, and then close it, saving if necessary.
18. Open the DrivingTours table in Datasheet View. Modify the expression in the TotalAdultCost field to subtract 2.00 from the calculation. (Hint: The final expression will be [NumAdult]*[AdultCost]-2). Save the changes to the table and close it.
19. Open the Guides table in Datasheet View. Add the Total row to the datasheet and calculate the Average for the HourlyRate field. Save the changes to the table and close it.
20. Open the Customers table in Datasheet View and display the Subdatasheet for the customer with CustomerID 10024. Update the ChildCost for ConfirmationID 1900118 to $10. Close the Customers table.

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.