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

Subject:MS AccessPrice:19.99 Bought3

Share With

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

Personal Insurance, Inc.

CREATING QUERIES

GETTING STARTED

• Open the file SC_AC19_CT2c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_CT2c_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_CT2c_UmbrellaData.xlsx
• 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., is a national company that insures homeowners and renters. It also offers umbrella policies that provide additional coverage. As a regional manager, you need to query the database to help make decisions and to answer employees' questions.
   
   Create a query based on the Customers table. Add the CustomerID, FirstName, LastName, and DateOfBirth fields to the query. Sort the records in ascending order by the LastName field, and then save the query with the name CustomerLastNameSorted. Close the query.
2. Make the following changes to the HomeownersPremium query:
   1. Delete the CustomerID field, and then select only records where the Premium field value is greater than 2,000. Save and close the query.
3. Update the LiabilityOrProperty query to select only those records where the Liability field value equals 75,000 or the PersonalProperty field value equals 75,000. Save and close the query.
4. Make the following changes to the ComparisonBirth query:
   1. Add the FirstName field immediately after the CustomerID field.
   2. Select only those records where the DateOfBirth field value is less than 1/1/1950.
   3. Update the DateOfBirth field to have the Medium Date format and to display BirthDate as the caption.
      Run the query, and then confirm that it matches Figure 1. Save and close the query.

Figure 1: ComparisonBirth query results

5. Make the following changes to the StateCitySort query:
   1. Move the State field so that it appears first in the query.
   2. Sort the records in descending order by the State field and in ascending order by the City field.
      Save and close the query.
6. Update the LiabilityAndProperty query to select only those records where the Liability field value equals 100,000 and the PersonalProperty field value equals 100,000. Save and close the query.
7. Update the MichiganCustomers query to select only those records where the State field value is "MI". Save and close the query.
8. Because customers of Personal Insurance reside in many different areas, it is often difficult to know the exact spelling of a city. Update the CustomerCity query to select only those records where the City field value begins with the letters "Al". (Hint: The letters are capital A and lowercase L, as in the first two letters of "alphabet".) Save and close the query.
9. Many queries require data from more than one table. For example, you may want a query to display the customer's last name rather than the customer ID for a renter's policy. Create a query based on the Customers and Renters tables with the following options:
   1. Include the LastName field from the Customers table and the PolicyNumber and Premium fields from the Renters table in the query.
   2. Join the Customers table and the Renters table using the CustomerID field.
   3. Save the query using Customer-Renter as the name. Close the query.
10. Because customers live in different states, it is often advantageous to create a query where you can change one criterion using a parameter query. Update the StatesParameter query as follows.
    1. Remove existing criteria on the State field.
    2. Add parameter criteria to the State field to prompt the user with Enter desired state as the text. Save and close the query.
11. Modify the TopValuesProperty query to sort the PropertyDamage amounts in descending order. Change the Return value to display only the top five records. Save and close the query.
12. Modify the TotalCoverage query by adding a calculated field named TotalCoverage that adds the Liability and PropertyDamage field values. Save and close the query.
13. Update the AveragePremium query by averaging the values of the Premium field and updating the field's caption to Avg Prem. Run the query and confirm that it matches Figure 2. Save and close the query.

Figure 2: AveragePremium query results

14. Update the CustomersWithoutHomes query as follows:
    1. Change the Join property for the relationship between the Customers and Homeowners tables to select all records from the Customers table and only those records from the Homeowners table where the joined fields are equal.
    2. Add the Is Null criteria for the PolicyNumber field, and then add an ascending sort order to the LastName field. Save the changes to the query. Open the query in Datasheet View and confirm that there are 21 records in the query result. Close the query, saving it if necessary.
15. Open the UniqueStates query in Design View. Modify the query to list all states only once. Save and close the query.
16. Create a crosstab query based on the MidAtlantic table with the following options:
    1. Use only data from the MidAtlantic table in the crosstab query.
    2. Use the State field for the row headings.
    3. Use the City field for the column headings.
    4. Use a count of the CustomerID field as the calculated value for each row and column intersection, and include row sums in the crosstab query.
       Save the query, using State-City Crosstab as the name. Close the query.
17. Export the Renters table as an Excel file (.xlsx) with the name Renters to the same folder as the one that stores your database. Do not export the data with formatting and layout. Save the export steps using Export-Renters as the name. Do not add a description. Save the changes to the table, and then close it.
18. Import the data from the Support_AC19_CT2c_UmbrellaData.xlsx support file, and then append it to the Umbrella table. Do not save the import steps.
19. In the Navigation Pane, change the name of the PennsylvaniaOwners query to PennsylvaniaCustomers.
20. Group the objects in the Navigation Pane using the Tables and Related Views option.

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.