right

-580505

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

0

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

Personal Insurance, Inc.

Creating Queries

3875

16510

0

0

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 STEP

S

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:

Add the

CustomerID

,

FirstName

,

LastName

, and

DateOfBirth

fields to the design grid in that order.

Sort

the records in

ascending

order by

LastName

.

Save the query using

CustomerLastNameSorted

as the name.

Open the query in Datasheet View, then close it, saving if necessary.

Open the

HomeownersPremium

query in Design View and make the following changes to the query:

Delete

the

CustomerID

column from the design grid.

Add criteria to select only those records where the

Premium

field value is

greater than

2,000

.

Save the changes to the

HomeownersPremium

query.

Open the query in Datasheet View, then close it, saving if necessary.

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.

Open the

ComparisonBirth

query in Design View and make the following changes to the query:

Add

the

FirstName

field to the query design grid. The

FirstName

field should immediately follow the

CustomerID

field.

Add criteria to select only those records where the

DateOfBirth

field value is

less than 1/1/1950

.

Set the format to

Medium Date

for the

DateOfBirth

field.

Set the caption to

BirthDate

for the

DateOfBirth

field.

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