Homework answers / question archive / Shelly Cashman Access 2019 | Module 3: SAM Critical Thinking Project 1c Personal Insurance, Inc

Shelly Cashman Access 2019 | Module 3: SAM Critical Thinking Project 1c Personal Insurance, Inc

MS Access

Share With

Shelly Cashman Access 2019 | Module 3: SAM Critical Thinking Project 1c

Personal Insurance, Inc.

MAINTAINING A DATABASE

GETTING STARTED

• Open the file SC_AC19_CT3c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_CT3c_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 its database on a regular basis. For example, customers move, change coverage limits, and add and delete policies. Create an Update query based on the Renters table as follows:
   1. Select the PolicyNumber and PersonalProperty fields from the Renters table, and only those records where the PolicyNumber field value equals R11234.
   2. Update the PersonalProperty field with 100,000 as the new field value.
   3. Save the query using UpdatePersonalProperty as the name, run it, and then close it.
2. 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 by completing the following tasks:
   1. 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.
3. Create a Delete query for the Renters table by completing the following tasks:
   1. Select the PersonalProperty field from the Renters table.
   2. Delete only those records where the PersonalProperty field value equals 45,000.
   3. Save the query using DeletePersonalProperty as the name, run it, and then close it.
4. Customers who reside in West Virginia should be appended to the MidAtlantic table. Create an Append query for the Customers table as follows:
   1. Select all the fields from the Customers table in the same order that they are listed in the field list.
   2. Add the criteria WV to the State field.
   3. Select MidAtlantic as the destination table.
   4. Save the query using AppendMidAtlantic as the name, run it, and then close it.
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 then delete the record. Close the Umbrella Split Form.
7. Because you are a regional manager for Personal Insurance, it is useful to know in which regions of the country each customer resides. Complete the following tasks in the Customers table:
   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.

8. With the Customers table still open, delete the CreditCard field. Save the change to the table.
9. Complete the following tasks in the Customers table:
   1. Add the caption US Region to the Region field.
   2. Set the Region field value to Midwest for Joaquin Howe (who has a CustomerID field value of 11002). Close the Customers table.
10. Complete the following tasks in the Claims table:
    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 property for the ClaimAmount field to Currency.
    4. Add a new calculated field named AmountDue after the Paid field. The AmountDue field should subtract the Paid field values from the ClaimAmount field values. Save the changes to the Claims table, and then close it.
11. Complete the following tasks in the Umbrella table:
    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.
    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. Add a new calculated field named TotalCoverage to the end of the table. The new TotalCoverage field should add the Liability and PropertyDamage field values. Save the changes to the table, and then close it.
13. Open the Claims table and complete 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 the ClaimAmount field. Save the changes to the Claims table, and then close it.
14. Open the Renters table 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 Must be greater than or equal to 0 as the validation text. 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 then add the Customers and Renters tables to the Relationships window. Create a one-to-many relationship between the Customers and Renters tables using the common CustomerID field. Enforce referential integrity on the relationship, then save and close the relationship.
16. Create a Find Unmatched query as follows to find all customers who do not have umbrella coverage:
    1. Select the Customers table as the table to display in the query results.
    2. Select the Umbrella table as the related table.
    3. Select the CustomerID field as the common field in both tables.
    4. Display all fields in the query results.
    5. Save the query using UnmatchedUmbrella as the name. View the query results. Save the query, if necessary, and then close it.
17. Open the TotalCoverage query. 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. Modify the expression in the TotalCoverage field to subtract the deductible from the calculation. Save the changes to the table, and then close it.
19. Open the Umbrella table. Add the total row to the datasheet, and then calculate the average for the Premium field. Save the changes to the table, and then close it.
20. Open the Customers table and display the subdatasheet for the customer with CustomerID 11004. Change 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.