Shelly Cashman Access 365/2021 | Module 9: SAM Project 1a

Personal Insurance, Inc.

Administering a Database System

GETTING STARTED

• Save the file SC_AC365_2021_9a_FirstLastName_1.accdb as SC_AC365_2021_9a_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_9a_ClaimTypes.accdb
• With the file SC_AC365_2021_9a_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

• Personal Insurance, Inc. is a national company that insures homeowners and renters. As a regional manager, 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.)
  Save the changes to the options. (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 ClaimsRep table in Design View, and then create a custom input mask for the ClaimRepNumber field as described below:
  • The ClaimRepNumber field must consist of two letters and two numbers.
  • All letters should display in upper case. (Hint: Enter >LL99 as the input mask.)
  • Save the changes to the table but do not close it.
• With the ClaimsRep table still open in Design View, modify the ClaimsRepName 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 ClaimsRep 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 Location 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 Claims 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 ClaimAmount field value. (Hint: Enter [Paid]<=[ClaimAmount] as the validation rule.
  • Enter Paid cannot exceed ClaimAmount 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 Claims table still open in Design View, create a multiple-field index as described below:
  • Use ClaimsInformation 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 ClaimID field, sorted in Ascending order, as the second field in the index.

    Save the changes to the Claims table, and then close it.

• Create a Datasheet form for the Umbrella table, and then save the form using UmbrellaForm 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_9a_ClaimTypes.accdb in Exclusive mode, using the password claims when the Password Required dialog box appears. Enable the content.
  • Decrypt the Support_AC365_2021_9a_ClaimTypes.accdb database. (Hint: You will need to use the password claims again in the Unset Database Password dialog box.) Close the database.
  • Open (or return to) the SC_AC365_2021_9a_FirstLastName_2.accdb database.
  • Import the ClaimTypes table from the database Support_AC365_2021_9a_ClaimTypes.accdb into a new table using ClaimTypes 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 AltRegion 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 CustomerID field is the primary key of the AltCustomers table as shown in Figure 3. Then, click the Next button.
  • Ensure that the “No, don’t create the 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 AltRegion tables.

    Confirm the Navigation Pane matches Figure 4.

• Switch to viewing database items by the custom category named “Claims Information” in the Navigation Pane. Add the following two forms to the Claims Information Forms group:
  • Claims Master Form
  • ClaimsRep and Claims Form

    Confirm the Claims Information Forms group matches Figure 5.

• Add a new group to the Claims Information category as described below:
  • Use Claims Reports as the name for the new group.
  • If necessary, move the Claims Reports group so that it appears between the Claims Information Forms and the Unassigned Objects groups.
  • In the Navigation Pane, add the Basic Claims Report to the new group.

    Confirm the Claims Reports group and 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 Claims 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.