Homework answers / question archive / Shelly Cashman Access 2019 | Module 2: SAM Project 1a Personal Insurance, Inc

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

MS Access

Share With

---

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

Personal Insurance, Inc.

CREATING QUERIES

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

?

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