Shelly Cashman Access 2016 | Module 2: SAM Project 1b All Around Outside Maintenance CREATING AND MODIFYING QUERIES GETTING STARTED ? Open the file SC_AC16_2b_ FirstLastName _1

Shelly Cashman Access 2016 | Module 2: SAM Project 1b

All Around Outside Maintenance

CREATING AND MODIFYING QUERIES

GETTING STARTED

?

Open the file

SC_AC16_2b_

FirstLastName

_1.accdb

, available for download

from the SAM website.

?

Save the file as

SC_AC16_2b_

FirstLastName

_2.accdb

by changing the “1” to

a “2”.

o

Hint

: If you do not see the

.accdb

file extension

in the Save As dialog box,

do not type it. Access 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.

PROJECT STEPS

1.

Open the

Property Listing Query

in Design View. Delete the

ClientNumber

field

from the query, and then save, run, and close the query.

2.

Create a new query in Design View based on the

Manager

table with the

following options:

a.

Add the fields

ManagerID

,

LastName

,

FirstName

, and

StartDate

to the

query in that order.

b.

Add an ascending sort to the

StartDate

field.

c.

Save the query with the name

Manager Date Query

.

Run the

Manager Date Query

, and then close it.

3.

Create a new parameter query in Design View based on the

Client

table with the

following options:

a.

Add the

ManagerID

,

ClientNumber

,

ClientNa

me,

City

, and

State

fields (in

that order) from the

Client

table to the query.

b.

Add the criterion

[Enter State]

(including brackets) to the

State

field.

c.

Save the query with the name

State Client Query

.

Run the

State Client Query

to confirm that it works. (

Hint

: If you use NJ for the

parameter value, the query should return three records.) Save and close the

query.

4.

Create a new query in Design View based on the

Manager

and

Client

tables with

the following options:

a.

Add the

ClientName

field from the

Client

table to the query.

b.

Add the

FirstName

,

LastName

, and

PhoneNumber

fields (in that order)

from the

Manager

table to the query.

c.

Save the query with the name

Manager Client Query

.

Run the

Manager Client Query

, and then save and close it.

5.

Open the

Unique Cities Query

in Design View. Modify the query to list all cities

only once. (

Hint

: Click the second field in the Design grid, open the property

sheet, and then change the Unique Values property for the query.) Run the

query, and then save and close it. (

Hint

: The query should return 17 records.)

6.

Create a crosstab query based on the

Client

table with the following options:

a.

Use only data from the

Client

table in the crosstab.

b.

Use the

State

field for the row headings.

c.

Use the

ManagerID

field for the column headings.

d.

Use the sum of the

AmountPaid

field as the calculated value for each row

and column intersection, and include row sums in the crosstab query.

e.

Save the query with the name

State-Manager Crosstab

.

View the query, and then save and close it.

7.

Open the

Top Values Query

in Design View. Modify the query to sort the Balance

amounts in descending order. Change the Return value to display only the top

25% of the records. Run the query, and then save and close it.

8.

Open the

Delaware Manager Query

in Design View. Add criteria to select only

those records where the

ManagerID

field value is equal to

KS02

and

the

State

field value is equal to

DE

. Run the query, and then save and close it.

9.

Open the

Total Amount Query

in Design View. Modify the query by creating a

calculated field to compute the total amount paid and balance. Enter

TotalAmount: [AmountPaid]+[Balance]

in the Zoom dialog box of the first

empty column in the design grid. Run the query, and then save and close it.

10.

Open the

Caption Query

in Design View. Modify the query by adding the caption

Owed

for the

Balance

field. Run the query, and then save and close the query.

11.

Open the

No Balance Query

in Design View. Modify the query to show only

records where the

Balance

field is equal to

0

. Run the query, and then save and

close it.

12.

Open the

New Jersey Properties Query

in Design View, hide the

State

field in the

query, and then save and close it. (

Hint

: Do not delete the

State

field from this

query.)

13.

Open the

MD DE Properties Query

in Design View. Add criteria to select only

those records where the

State

field is equal to

MD

or

DE

. Run the query, and

then save and close it.

14.

Open the

Multiple Sort

query in Design View. Modify the query to sort the

records in ascending order first by the

ManagerID

field and then by the

ClientName

field. Run the query, and then save and close it.

15.

Open the

Small Balances Query

in Design View. Add criteria to select only those

records where the value in the

Balance

field is less than

2000

. Run the query,

and then save and close it.

16.

Open the

Amount Paid by Manager Query

in Design View and modify it by

adding Totals to the query, as described below:

a.

For the

ManagerID

field, set the total row to

Group By

.

b.

For the

AmountPaid

field, set the total row to

Sum

.

Run the query, and then save and close it.

17.

Open the

M Client Query

in Design View. Add the

M*

wildcard criteria to the

ClientName

field, so that the query only returns records when the client name

begins with M. Run the query, and then save and close it.

18.

Use the Report Wizard to create a new report (shown in Figure 1 on the next

page) based on the

All Clients Query

with the following options:

a.

Include all fields from the

All Clients Query

.

b.

Group the report by the

LastName

field and use no additional grouping in

the report.

c.

Use no additional sorting in the report.

d.

Use the

Stepped

layout and

Portrait

orientation for the report.

e.

Set the title of the report to

All Clients Report

.

Preview the report, and then save and close it.

Figure 1: All Clients Report

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.