Homework answers / question archive / Shelly Cashman Access 2019 | Module 9: SAM Project 1b City Tours Ltd

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

MS Access

Share With

---

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

City Tours Ltd.

ADMINISTERING A DATABASE SYSTEM

 

GETTING STARTED

• Open the file SC_AC19_9b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_9b_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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_AC19_9b_TourTypes.accdb
• 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. is a company that provides tours in a city that attracts tourists from around the country. As the customer service manager for the company, you need to understand database administration tasks.
   Open the database in Backstage View and then click the View and edit database properties link. When the Properties dialog box displays, create a custom property for the database as described below:
   1. Create a custom property with Tested as the property name.
   2. Select Date as the type.
   3. Enter 06/20/2020 as the value.
      Confirm that your custom property matches the one shown in Figure 1. (Hint: Your database may contain other properties, depending on the version of Access used to open the file or the method by which the file was downloaded from SAM. Ignore these additional properties.) Click Ok.

2. With the database still open in Backstage View, click the Options tab. Select the Main Menu Navigation Form as the Display Form for the database as shown in Figure 2. (Hint: The Display Form is sometimes referred to as the startup form.) Click Ok. (Hint: When the Access dialog box appears and displays the message, "You must close and reopen the current database for the specified option to take effect", click the OK button.)

3. Open the Tours table in Design View, and then create a custom input mask for the TourNumber field as described below:
   1. The TourNumber field must consist of one letter and three numbers.
   2. The letter should display in upper case. (Hint: Enter >L999 as the input mask.)
      
      Save the change to the table and then close it.
4. Open the Guides table in Design View and modify the GuideName multiple-field index by completing the following tasks:
   1. Open the Indexes dialog box.
   2. Change the Sort Order for the LastName field to Descending.
      
      Save the changes to the Guides table and then close it.
5. Open the Customers table in Design View, and then create a single-field index on the LastName field. The index should allow duplicate values. Save the changes to the table design but do not close the table.
6. With the Customers table still open in Design View, create a multiple-field index as described below:
   1. Use Address as the name of the index. (Hint: Be careful not to include spaces after the index name.)
   2. Use the State field, sorted in Descending order, as the first field in the index.
   3. Use the City field, sorted in Ascending order, as the second field in the index.
      
      Save the changes to the Customers table and then close it.
7. Open the SegwayTours table in Design View, and then open the Property Sheet for the table and make the following updates:
   1. Create a validation rule for the table requiring that the Paid field value is always less than or equal to the TotalCost field value. (Hint: Enter [Paid]<=[TotalCost] as the validation rule.)
   2. Enter Paid cannot exceed TotalCost as the validation text.
      Close the Property Sheet, and then save the changes to the table. (Hint: Because there was a change to data integrity rules, the "existing data may not be valid" warning message will appear. Click No and continue saving the table.)
8. With the SegwayTours table still open in Design View, create a multiple-field index as described below:
   1. Use TourInformation as the name of the index. (Hint: Be careful not to include spaces after the index name.)
   2. Use the CustomerID field, sorted in Ascending order, as the first field in the index.
   3. Use the TourNumber field, sorted in Ascending order, as the second field in the index.
      
      Save the changes to the SegwayTours table, and then close it.
9. Create a Datasheet form for the HomeTours table, and then save the form using HomeToursForm as the form name. Close the form.
10. Create a blank form based on the 1 Right application part. (Hint: The blank form will appear in the Navigation Pane as a forms object named SingleOneColumnRightLabels and is saved automatically.)
11. Import a table from an encrypted database by completing the following tasks:
    1. Open the encrypted database Support_AC19_9b_TourTypes.accdb in Exclusive mode, using the password tours when the Password Required dialog box appears. Enable the content.
    2. Decrypt the Support_AC19_9b_TourTypes.accdb database. (Hint: You will need to use the password tours again in the Unset Database Password dialog box.) Close the database.
    3. Open (or return to) the SC_AC19_9b_FirstLastName_2.accdb database.
    4. Import the TourTypes table from the database Support_AC19_9b_TourTypes.accdb into a new table using TourTypes as the table name. (Hint: Ensure that the Tables tab is selected in the Import Objects dialog box.)
    5. Do not save the Import steps.
12. Use the Table Analyzer to analyze the Customers table for redundancy. Implement the following options when using the Table Analyzer Wizard:
    1. Select the Customers table as the table to analyze, and let the wizard decide what fields go in each table.
    2. Rename Table 1 as AltCustomers using the Rename table button as shown in Figure 3.
    3. Rename Table 2 as AltRegions using the Rename table button as shown in Figure 3.
    4. When the wizard displays the next screen with the question, "do the bold fields uniquely identify each record in the proposed table", make sure the Generated Unique ID field is the primary key of the AltRegions table as shown in Figure 3. Then, click the Next button.
    5. Ensure that the "No, don't create a query" radio button is selected, then click Finish to close the Table Analyzer Wizard.
    6. When the Table Analyzer Wizard dialog box appears with the message, "The command or action TileHorizontally isn't available now", click OK. Close the AltCustomers and AltRegions tables.
       
       Confirm the Navigation Pane matches Figure 4.

13. Switch to viewing database items by the custom category named "Guide Information" in the Navigation Pane. Add the following two reports to the Guides Information Reports group:
    1.  Basic Guide List
    2.  Guide Skills Report
       
       Confirm the Guide Information Reports group matches Figure 5.
14. Add a new group to the Guide Information category as described below:
    1. Use Guide Information Forms as the name for the new group.
    2. If necessary, move the Guide Information Forms group so that it appears immediately before the Guide Information Reports.
    3. In the Navigation Pane, add the Guides Update Form to the new group.
       
       Confirm the Guide Information Forms group and the Guides Update Form item in the Navigation Pane match Figure 5.

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.