GETTING STARTED

· Open the file
SC_AC19_2a_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as
SC_AC19_2a_FirstLastName_2.accdb
by changing the “1” to a “2”.

o 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:

o Support_AC19_2a_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 be able to query the database to help make decisions and to answer questions from other employees.

Create a query based on the
Customers
table in Query Design View with the following options:

a. Add the
CustomerID,
FirstName,
LastName, and
DateOfBirth
fields to the design grid in that order.

b.
Sort
the records in
ascending
order by
LastName.

c. Save the query using
CustomerLastNameSorted
as the name.
Open the query in Datasheet View, then close it, saving if necessary.

2. Open the
HomeownersPremium
query in Design View and make the following changes to the query:

a.
Delete
the
CustomerID
column from the design grid.

b. Add criteria to select only those records where the
Premium
field value is
greater than
2,000.

c. Save the changes to the
HomeownersPremium
query.
Open the query in Datasheet View, then close it, saving if necessary.

3. Open the
LiabilityOrProperty
query in Design View and add criteria to select only those records where the
Liability
field values
equal
75,000
or
the
PersonalProperty
field values
equal
75,000. Save the changes to the query. Open the query in Datasheet View, confirm that 3 records appear in the
LiabilityOrProperty
query results, then close the query, saving if necessary.

4. Open the
ComparisonBirth
query in Design View and make the following changes to the query:

a.
Add
the
FirstName
field to the query design grid. The
FirstName
field should immediately follow the
CustomerID
field.

b. Add criteria to select only those records where the
DateOfBirth
field value is
less than 1/1/1950.

c. Set the format to
Medium Date
for the
DateOfBirth
field.

d. Set the caption to
BirthDate
for the
DateOfBirth
field.

e. Save the changes to the
ComparisonBirth
query.
Open the query in Datasheet View, confirm that the results match Figure 1, then close it, saving if necessary.

Figure 1: ComparisonBirth query results

5. Open the
StateCitySort
query in Design View and make the following changes:

a. Move the
State
field to the
beginning
of the design grid so that the order of the fields in the grid is
State,
City,
FirstName, and
LastName.

b.
Sort
the records in
descending
order by the
State
field and in
ascending
order by
City.
Save the changes to the query. View the query in Datasheet View, then close it, saving if necessary.

6. Open the
LiabilityAndProperty
query in Design View and add criteria to select only those records where the
Liability
field values
equal
100,000
and
the
PersonalProperty
field values
equal
100,000. Save the changes to the query. Open the
LiabilityAndProperty
query in Datasheet View, confirm that 2 records appear in the query results, then close the query, saving if necessary.

7. Open the
MichiganCustomers
query in Design View and add criteria to select only those records where the
State
field value is
MI. Save the changes to the query. Open the query in Datasheet View, confirm that 7 records appear in the query results, then close the query, saving if necessary.

8. Because customers of Personal Insurance reside in many different cities, it is often difficult to know the exact spelling of a city. Open the
CustomerCity
query in Design View and add criteria to select only those records where the
City
field value
begins with the letters Al. Save the changes to the query. Open the query in Datasheet View, confirm that 2 records appear in the query results, then close the query, saving if necessary.

9. Many queries require data from more than one table. For example, you may want a query to display the customer last name rather than the customer ID for a renter's policy. Create a query in Design View based on the
Customers
and
Renters
tables with the following options:

a. Add the
Customers
table and the
Renters
table to the design window.

b. Add the
LastName
field from the
Customers
table to the design grid.

c. Add the
PolicyNumber
and
Premium
field from the
Renters
table to the design grid.

d.
Join
the
Customers
table and the
Renters
table by drawing a line from the
CustomerID
field in the
Customers
table to the
CustomerID
field in the
Renters
table. (Hint: Because the field names are identical in both tables, the line may already be there.)

e. Save the query using
Customer-Renter
as the name.
Open the query in Datasheet View, then close it, saving if necessary.

10. Because customers live in different states, it is often advantageous to create a query where you can change one criteria using a parameter query. Open the
StatesParameter
query in Design View.

a. Add parameter criteria to the
State
field to replace the current "FL" criteria. The new parameter criteria should prompt the user with
Enter desired state
as the text.

b. Save the changes to the query.

c. View the query in Datasheet View. Enter
PA
when prompted.
Confirm that 8 records appear in the query results then close the query, saving if necessary.

11. Open the
TopValuesProperty
query in Design View. Modify the query to
sort
the
PropertyDamage
amounts in
descending
order. Change the Return value to display only the top
5
records. Open the query in Datasheet view, then close the query, saving if necessary.

12. Open the
TotalCoverage
query in Design View. Modify the query by creating a calculated field. Enter
TotalCoverage: [Liability]+[PropertyDamage]
in the Zoom dialog box of the first empty column in the design grid. Save the query. Open the query in Datasheet View, then close the query, saving if necessary.

13. Open the
AveragePremium
query in Design View and perform the following tasks:

a. Add a
Totals
row to the design grid.

b. Select
Avg
as the calculation in the Total row.

c. Set the caption to
Avg Prem
for the Premium field.
Save the changes to the query. Open the query in Datasheet View and confirm that it matches Figure 2. Close the query, saving if necessary.

Figure 2: AveragePremium query results

14. Open the
CustomersWithoutHomes
query in Design View and perform the following tasks:

a. 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.

b. Add the
Is Null
criteria for the
PolicyNumber
field and add an
Ascending
sort order on 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 if necessary.

15. Open the
UniqueStates
query in Design View. Modify the query to list all states only once. Save the changes to the query. Open the query in Datasheet View and confirm that there are 15 records in the query result. Close the query, saving if necessary.

16. Create a crosstab query based on the
MidAtlantic
table with the following options:

a. Use only data from the
MidAtlantic
table in the crosstab.

b. Use the
State
field for the row headings.

c. Use the
City
field for the column headings.

d. 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.

e. Save the query using
State-City Crosstab
as the name.
View the query, then save and close it.

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 close it.

18. Use the Import Spreadsheet Wizard to import the data from the
Support_AC19_2a_UmbrellaData.xlsx
support file and
append
it to the
Umbrella
table. Do not save the Import steps. Open the
Umbrella
table in Datasheet View. It should contain 14 records. Close the table.

19. Rename the
PennsylvaniaOwners
query as
PennsylvaniaCustomers
in the Navigation Pane.

20. Group the objects in the Navigation Pane by
Tables and Related Views.

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.