Shelly Cashman Access 2019 | Module 3: SAM Project 1a Personal Insurance, Inc

Subject:MS AccessPrice:19.99 Bought4

Share With

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

Personal Insurance, Inc.

Maintaining a Database

• GETTING STARTED

• Open the file SC_AC19_3a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_3a_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. Personal Insurance, Inc. needs to update their database on a regular basis. For example, customers move, change coverage limits, and add and delete policies. In Query Design View, create an Update query based on the Renters table.
   1. Select the PolicyNumber and PersonalProperty fields from the Renters table.
   2. Add criteria to select only those records where the PolicyNumber field values equal R11234.
   3. Update the PersonalProperty field with 100,000 as the new field value.
   4. Save the query using UpdatePersonalProperty as the name, run it, and then close it. The query should update one record.
2. In Query Design View, create a Make Table query that selects all the fields from the Homeowners 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 PropertyDamage field value is greater than or equal to 750,000.
   2. In the Make Table dialog box, assign the name HighDamageAmts to the new table.
   3. Save the query using MakeHighDamageAmts as the name, run it, and then close the query. The new HighDamageAmts table should contain five records.
3. In Query Design View, create a Delete query for the Renters table by performing the following tasks:
   1. Select the PersonalProperty field from the Renters table.
   2. Add criteria to delete only those records where the PersonalProperty field values equal 45,000.
   3. Save the query using DeletePersonalProperty as the name, run it, and then close it. The query should delete one record.
4. Customers who reside in West Virginia should be appended to the MidAtlantic 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 WV to the State field.
   3. Select MidAtlantic as the destination table in the Append dialog box.
   4. Save the query using AppendMidAtlantic as the name, run it, and then close it. The query should append two records.
5. Create a Split Form for the Umbrella table and save it, using Umbrella Split Form as the name. Do not close the form.
6. Switch the Umbrella Split Form to Form View, navigate to the record with the PolicyNumber field value U10002, and delete the record. Close the Umbrella Split Form.
7. Because you are a regional manager for Personal Insurance, Inc., it is useful to know in which regions of the country each customer resides. Open the Customers table in Design View and perform the following tasks:
   1. Move the Region field so that it immediately follows the PostalCode field. Save the changes to the table design.
   2. Use the Lookup Wizard to change the Region 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 Region Lookup Field

 

MidAtlantic

Midwest

Northeast

Southeast

 

8. With the Customers table still open in Design View, delete the CreditCard field. Save the change to the table.
9. Switch to Datasheet View and perform the following tasks:
   1. Add the caption US Region to the Region field.
   2. Change the Region field value to Midwest for Joaquin Howe (who has a CustomerID field value of 11002). Close the Customers table.
10. Open the Claims table in Design View and perform the following tasks:
    1. Make the ClaimID field the primary key.
    2. Change the field size property for the CustomerID field to 6.
    3. Change the data type for the ClaimAmount field to Currency.
    4. Add a new field AmountDue after the Paid field. The field is a calculated field. Type the expression ClaimAmount - Paid to calculate the field values.
       
       Save the changes to the Claims table and then close it.
11. Open the Umbrella table in Design View and perform the following tasks:
    1. Change the Decimal Places property for the Coverage field to 0.
    2. Change the Format property for the PolicyNumber field so that any letters in the field display in uppercase. (Hint: Use the > symbol.)
    3. Enter Additional Liability Coverage as the Description for the Coverage field.
       
       Save the changes to the Umbrella table and then close it.
12. Open the Homeowners 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 [Liability]+[PropertyDamage] to calculate the field values. Assign the name TotalCoverage to the field. Save the changes to the table and close it.
13. Open the Claims table in Datasheet View and perform the following tasks:
    1. Resize the PolicyNumber, ClaimAmount, and ClaimApproved columns to display the complete field names.
    2. Remove the Total row from the table.
    3. Sort the records in ascending order by ClaimAmount.
       
       Save the changes to the Claims table and then close it.
14. Open the Renters table in Design View and make the following changes:
    1. Enter a Validation Rule for the Deductible field to ensure that values in the field are greater than or equal to 0.
    2. Enter the text, Must be greater than or equal to 0 in the Validation Text property box.
       
       Save the changes to the Renters 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 Renters 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 Renters 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 umbrella coverage.
    1. Select the Customers table as the table to display in query results.
    2. Select the Umbrella 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 UnmatchedUmbrella as the name.
       
       View the query results. There should be 38 records. Save the query, if necessary, and close it.
17. Open the TotalCoverage query in Design View. Use the Expression Builder to create a calculated field at the end of the query grid that calculates the sum of the Liability and PropertyDamage fields. Assign the alias (caption) TotalCoverage to the calculated field. Open the query in Datasheet View, and then close it, saving if necessary.
18. Open the Renters table in Datasheet View. Modify the expression in the TotalCoverage field to subtract the Deductible from the calculation. (Hint: The final expression will be [Liability]+[PersonalProperty]-[Deductible]). Save the changes to the table and close it.
19. Open the Umbrella table in Datasheet View. Add the Total row to the datasheet and calculate the Average for the Premium 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 11004. Update the Deductible for policy R10223 to $1,000. 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.