City Tours Ltd.
ADMINISTERING A DATABASE SYSTEM
GETTING STARTED
Save th
City Tours Ltd.
ADMINISTERING A DATABASE SYSTEM
GETTING STARTED
Save the file SC_AC365_2021_9b_FirstLastName_1.accdb as SC_AC365_2021_9b_FirstLastName_2.accdb Edit the file name by changing “1” to “2”.
If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
To complete this Project, you will also need the following files:Support_AC365_2021_9b_TourTypes.accdb
With the file SC_AC365_2021_9b_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table. If the table does not display your name, delete the file and download a new copy.
PROJECT STEPS
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:
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.)
Open the Tours table in Design View, and then create a custom input mask for the TourNumber field as described below:The TourNumber field must consist of one letter and three numbers.
The letter should display in upper case. (Hint: Enter >L999 as the input mask.)
Save the change to the table and then close it.
Open the Guides table in Design View and modify the GuideName multiple-field index by completing the following tasks:Open the Indexes dialog box.
Change the Sort Order for the LastName field to Descending.
Save the changes to the Guides table and then close it.
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.
With the Customers table still open in Design View, create a multiple-field index as described below: Use Address as the name of the index. (Hint: Be careful not to include spaces after the index name.)
Use the State field, sorted in Descending order, as the first field in the index.
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.
Open the SegwayTours table in Design View, and then open the Property Sheet for the table and make the following updates: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.)
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.)
With the SegwayTours table still open in Design View, create a multiple-field index as described below:Use TourInformation as the name of the index. (Hint: Be careful not to include spaces after the index name.)
Use the CustomerID field, sorted in Ascending order, as the first field in the index.
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.
Create a Datasheet form for the HomeTours table, and then save the form using HomeToursForm as the form name. Close the form.
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.)
Import a table from an encrypted database by completing the following tasks:Open the encrypted database Support_AC365_2021_9b_TourTypes.accdb in Exclusive mode, using the password tours when the Password Required dialog box appears. Enable the content.
Decrypt the Support_AC365_2021_9b_TourTypes.accdb database. (Hint: You will need to use the password tours again in the Unset Database Password dialog box.) Close the database.
Open (or return to) the SC_AC365_2021_9b_FirstLastName_2.accdb database.
Import the TourTypes table from the database Support_AC365_2021_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.)
Do not save the Import steps.
Use the Table Analyzer to analyze the Customers table for redundancy. Implement the following options when using the Table Analyzer Wizard:Select the Customers table as the table to analyze, and let the wizard decide what fields go in each table.
Rename Table 1 as AltCustomers using the Rename table button as shown in Figure 3.
Rename Table 2 as AltRegions using the Rename table button as shown in Figure 3.
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.
Ensure that the "No, don't create a query" radio button is selected, then click Finish to close the Table Analyzer Wizard.
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.
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: Basic Guide List
Guide Skills Report
Confirm the Guide Information Reports group matches Figure 5.
Add a new group to the Guide Information category as described below:Use Guide Information Forms as the name for the new group.
If necessary, move the Guide Information Forms group so that it appears immediately before the Guide Information Reports.
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.
Figure 1: Custom Properties Tab in the Properties Dialog Box
Figure 2: Access Options Dialog Box
Figure 3: Table Analyzer Wizard
Figure 4: Navigation Pane with Tables Created in Step 12
Figure 5: Navigation Pane Viewed by Guide Information Category
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 website to submit your completed project.Shelly Cashman Access 365/2021 | Module 9: SAM Project 1b
City Tours Ltd.
ADMINISTERING A DATABASE SYSTEM
GETTING STARTED
Save the file SC_AC365_2021_9b_FirstLastName_1.accdb as SC_AC365_2021_9b_FirstLastName_2.accdb Edit the file name by changing “1” to “2”.
If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
To complete this Project, you will also need the following files:Support_AC365_2021_9b_TourTypes.accdb
With the file SC_AC365_2021_9b_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table. If the table does not display your name, delete the file and download a new copy.
PROJECT STEPS
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:
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.)
Open the Tours table in Design View, and then create a custom input mask for the TourNumber field as described below:The TourNumber field must consist of one letter and three numbers.
The letter should display in upper case. (Hint: Enter >L999 as the input mask.)
Save the change to the table and then close it.
Open the Guides table in Design View and modify the GuideName multiple-field index by completing the following tasks:Open the Indexes dialog box.
Change the Sort Order for the LastName field to Descending.
Save the changes to the Guides table and then close it.
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.
With the Customers table still open in Design View, create a multiple-field index as described below: Use Address as the name of the index. (Hint: Be careful not to include spaces after the index name.)
Use the State field, sorted in Descending order, as the first field in the index.
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.
Open the SegwayTours table in Design View, and then open the Property Sheet for the table and make the following updates: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.)
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.)
With the SegwayTours table still open in Design View, create a multiple-field index as described below:Use TourInformation as the name of the index. (Hint: Be careful not to include spaces after the index name.)
Use the CustomerID field, sorted in Ascending order, as the first field in the index.
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.
Create a Datasheet form for the HomeTours table, and then save the form using HomeToursForm as the form name. Close the form.
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.)
Import a table from an encrypted database by completing the following tasks:Open the encrypted database Support_AC365_2021_9b_TourTypes.accdb in Exclusive mode, using the password tours when the Password Required dialog box appears. Enable the content.
Decrypt the Support_AC365_2021_9b_TourTypes.accdb database. (Hint: You will need to use the password tours again in the Unset Database Password dialog box.) Close the database.
Open (or return to) the SC_AC365_2021_9b_FirstLastName_2.accdb database.
Import the TourTypes table from the database Support_AC365_2021_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.)
Do not save the Import steps.
Use the Table Analyzer to analyze the Customers table for redundancy. Implement the following options when using the Table Analyzer Wizard:Select the Customers table as the table to analyze, and let the wizard decide what fields go in each table.
Rename Table 1 as AltCustomers using the Rename table button as shown in Figure 3.
Rename Table 2 as AltRegions using the Rename table button as shown in Figure 3.
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.
Ensure that the "No, don't create a query" radio button is selected, then click Finish to close the Table Analyzer Wizard.
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.
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: Basic Guide List
Guide Skills Report
Confirm the Guide Information Reports group matches Figure 5.
Add a new group to the Guide Information category as described below:Use Guide Information Forms as the name for the new group.
If necessary, move the Guide Information Forms group so that it appears immediately before the Guide Information Reports.
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.
Figure 1: Custom Properties Tab in the Properties Dialog Box
Figure 2: Access Options Dialog Box
Figure 3: Table Analyzer Wizard
Figure 4: Navigation Pane with Tables Created in Step 12
Figure 5: Navigation Pane Viewed by Guide Information Category
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 website to submit your completed project.