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

Subject:MS AccessPrice:19.99 Bought5

Share With

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

Personal Insurance, Inc.

Using SQL

GETTING STARTED

• Open the file SC_AC19_10a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_10a_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.
• Special Note: SQL Queries
  
  To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands:
  
  · Do not enclose field names and table names in square brackets [] when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets.
  · Use parentheses () in your SQL code only when specified.
  · All SQL commands should end with a semicolon (;).
  · Only modify the aspects of the SQL commands that are specified in the assignment steps.

• PROJECT STEPS

1. Personal Insurance, Inc. is a national company that insures homeowners and renters. As a regional manager, you need to be able to use the extended management capabilities available through SQL. Create a new query in SQL View based on the ClaimTypes table with the following requirements:
    
   1. Add all fields from the ClaimTypes table to the new query using the asterisk (*) in the SELECT clause.
   2. Be sure to end the SQL command with a semicolon (;).
   3. Run the query to produce the results shown in Figure 1.
   4. Save the query using ClaimTypesQuery as the query name and then close the query.

Figure 1: ClaimTypesQuery Results

2. Open the ClaimsRepQuery in SQL View, and then add the LastName field to the SELECT clause after the FirstName field. Run the query and confirm that the results match those shown in Figure 2. Save and close the ClaimsRepQuery.

Figure 2: ClaimsRepQuery Results

3. Open the TotalCoverageQuery in SQL View. Add a computed field to the SELECT clause (after the Liability field) as described below:
    
   1. The computed field should calculate the total coverage for the insurance policy by adding the Liability and the PropertyDamage field values. (Hint: Enter Liability + PropertyDamage as the computation.)
   2. Use TotalCoverage as the name (alias) for this computed field. (Hint: Use the AS clause.)
   3. Run the query and confirm that the results match those shown in Figure 3. (Hint: Your records may be in a different order. Only a portion of the query results is shown in Figure 3.)
      Save and close the TotalCoverageQuery.

Figure 3: Portion of TotalCoverageQuery Results

4. Open the PremiumCriteriaQuery in SQL View. Add a WHERE clause to the query in the position shown in Figure 4 that restricts retrieval to only those policies where the premium is greater than 2000. (Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results to confirm that eight records meet the criteria. Save and close the PremiumCritieraQuery.

Figure 4: PremiumCriteriaQuery in SQL View

5. Open the CustomerRegionsQuery in SQL View. Add an OR clause to the WHERE clause in the query so that the query retrieves only those records in which the Region field is equal to Northeast or MidAtlantic. (Hint: Currently, the query only retrieves records where the Region field is equal to Northeast.) Run the query and ensure that the results match those shown in Figure 5. (Hint: Your records may be in a different order. Only a portion of the query results is shown in Figure 5.) Save and close the CustomerRegionsQuery.

Figure 5: Portion of CustomerRegionsQuery

6. Open the CustomerCityStateQuery in SQL View. Add an AND clause to the WHERE clause in the query so that the query retrieves only those records in which the State field is equal to PA and the City field is equal to Lansdale. (Hint: Currently, the query only retrieves records in which the State field is equal to PA.) Run the query, then confirm that the results match those shown in Figure 6. Save and close the CustomerCityStateQuery.

Figure 6: CustomerCityStateQuery Results

7. Open the MichiganCountQuery in SQL View. Modify the SELECT clause to count the number of customers that are located in MI. (Hint: Change the SELECT clause to COUNT(CustomerID). Use the AS clause to set the alias of the function result to StateCount. Run the query and confirm that the results match those shown in Figure 7. Save and close the MichiganCountQuery.

Figure 7: MichiganCountQuery Results

8. Open the JoinClaimsClaimRepQuery in SQL View. Add a WHERE clause that joins the Claims table and the ClaimsRep table. The common field in both tables is ClaimRepNumber. You will need to qualify the ClaimRepNumber field in the WHERE clause. Run the query, and confirm that the results match those shown in Figure 8. (Hint: Your records may be in a different order.) Save and close the query.

Figure 8: JoinClaimsClaimRepQuery

9. Open the UmbrellaLiabilityQuery in SQL View. Add a caption to the Coverage field in the SELECT clause. (Hint: Use the AS clause.) Use AddLiability as the caption for the Coverage field. Run the query, and confirm that the last field in the query displays as AddLiability, as shown in Figure 9. Save and close the UmbrellaLiabilityQuery.

Figure 9: UmbrellaLiabilityQuery Results

10. Open the StateSortQuery in SQL View. Modify the query to sort the records in ascending order by the State field. (Hint: Use the ORDER BY clause.) Run the query and confirm that the results match those shown in Figure 10. Save and close the StateSortQuery.

Figure 10: StateSortQuery Results

11. Open the GroupbyStateQuery in SQL View. Modify the query by completing the following tasks:
     
    1. Add the State field to the SELECT statement. The State field should appear before the COUNT(CustomerID) function.
    2. Group the records by the State field.
    3. Sort the records by the State field in ascending order.
       
       Run the query and confirm that the results match those shown in Figure 11. Save and close the GroupbyStateQuery.

Figure 11: GroupbyStateQuery Results

12. Open the MidAtlanticSortQuery in SQL View. Modify the query to sort the records first in descending order by the State field, and then in ascending order by the City field. (Hint: Use the ORDER BY clause.) Run the query, and confirm that the results match those shown in Figure 12. Save and close the MidAtlanticSortQuery.

Figure 12: MidAtlanticSortQuery Results

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.