Illustrated Access 2016 | Module 6: SAM Project 1b

Salvage Database

IMPROVING QUERIES

GETTING STARTED

Open the file

IL_AC16_6b_

FirstLastName

_1.accdb

, available for download

from the SAM website.

?

Save the file as

IL_AC16_6b_

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

PROJECT STEPS

1.

Create a new query in Design View based on the

Deposits

table with the

following options:

a.

Add the

LocID

and the

Weight

fields from the

Deposits

table (in that order)

to the query.

b.

Add the Totals row to the query (in Design View), then sum the

Weight

field.

c.

Save the query with the name

TotalsByLocID

, then run the query. (

Hint

:

The

Weight

field will be renamed “SumOfWeight” when you run this

query.)

2.

With the

TotalsByLocID

query open in Datasheet View, add the Total row to the

datasheet and sum the

SumOfWeight

field in the datasheet, as shown in Figure

1 on the next page. Save and close the

TotalsByLocID

query.

Figure 1: TotalsByLocID Query in Datasheet

View

3.

Right-click the

LocationDeposits

query, then copy and paste using

LargeRockHillDeposits

as the name.

4.

Open the

LargeRockHillDeposits

query in Design View, then add criteria to

select only those records that equal

Rock Hill Volunteers

in the

OrgName

field

and a

Weight

field value of greater than or equal to

200

. (

Hint

: Records

returned by this query should meet both criteria.) Switch to Datasheet View,

confirm your query returns the records shown in Figure 2 below, then save and

close the

LargeRockHillDeposits

query.

Figure 2: LargeRockHillDeposits Query

5.

Right-click the

LocationDeposits

query, then copy and paste it with the name

2020orLarge

. Open the

2020orLarge

query in Design View, then make the

following updates:

a.

Add criteria to select all records with a

DepositDate

field value of

2020

or

a

Weight

field value of greater than or equal to

300

. (

Hint

: An asterisk

should be used in place of the month and date in the

DepositDate

field

criteria. Records returned by this query should meet one or both criteria.)

b.

Add two sort orders to sort the records in

ascending

order first by the

DepositDate

field value, then in

ascending

order by the

Weight

field

value.

Switch to viewing the

2020orLarge

query in Datasheet View, confirm the query

returns the records shown in Figure 3 below, then save and close the

2020orLarge

query.

Figure 3: 2020orLarge Query

6.

Create a new query in Design View based on the

Organizations

and

Deposits

tables with the following options:

a.

Use the

OrgName

and

OrgCity

fields from the

Organizations

table, and the

DepositDate

and

Weight

fields from

Deposits

table (in that order) in your

query.

b.

Add criteria using the

In

operator to select all records in which the

OrgCity

field value is equal to

Destin

or

Seaside

.

c.

Save the query with the name

DestinSeaside

, switch to Datasheet View

to confirm the query matches Figure 4 below, then close the

DestinSeaside

query.

Figure 4: DestinSeaside Query

7.

Use the Crosstab Query Wizard to build a crosstab query based on the

DepositsByLocationAndOrg

query as described below:

a.

Select the

OrgName

field for the Row headings.

b.

Select the

LocName

field for the column headings.

c.

Select

Weight

as the calculated field.

d.

Select the

Sum

as the function.

e.

Enter

LocationCrosstab

as the query name.

f.

Confirm the query appears as shown in Figure 5 below, then close the

LocationCrosstab

query.

Figure 5: LocationCrosstab Query

8.

Open the

DepositRevenue

query in Design View, then create a calculated field

to the right of the

Weight

field named

HaulingFee

that multiplies the

Weight

field value by

20%

. (

Hint

: The calculated field expression should be

[Weight]*0.2

). Save the

DepositRevenue

query, display it in Datasheet View—

the first few records of which are shown in Figure 6 below—then close the

DepositRevenue

query.

Figure 6: DepositRevenue Query in Datasheet View

9.

Open the

ServiceYears

query in Design View, then create a calculated field to

the right of the

VolJoinDate

field named

ServiceYrs

using the Zoom dialog box.

Create the new field with the following expression that uses the

Int

(Integer)

and

Now

(Today’s date) functions to calculate the number of years from the

volunteer’s join date:

Int((Now()-[VolJoinDate])/365)

Save the

ServiceYears

query, display it in Datasheet View, as shown in Figure 7

below, then close the query. (

Hint

: Depending on the date you complete this

assignment, the values in the

ServiceYrs

column in your query may be greater

than those shown in Figure 7.)

Figure 7: ServiceYears Query in Datasheet View

10.

Open the

DepositsByOrg

query in Design View, then add the Totals row to the

query. The query should be grouped by the

OrgName

field, should count the

values in the

DepositDate

field, and should sum the values in the

Weight

field.

Save and run the query, then close it.

11.

Open the

DepositsByLocationAndOrg

query in Design View, then add a sort to

the query in

ascending

order by the

LocName

field, an

ascending

order by

the

OrgName

field, and an

ascending

order by the

DepositDate

field. Save and

run the query, then close it.

12.

Create a crosstab query in Design View based on the

LocationDeposits

query

with the following options:

a.

Add the

LocName

,

OrgName

, and

Weight

fields to the query (in that

order).

b.

Use the

LocName

field as the row heading.

c.

Use the

OrgName

field as the column heading.

d.

Use the

Weight

field as the value for the crosstab query, and use the SUM

function for the Total row calculation for this field.

e.

Save the query with the name

OrgCrosstab

and run it, as shown in Figure

8 on the next page, then close the query.

Figure 8: OrgCrosstab Query

13.

Use the Report Wizard to create a report based on the

DepositsByLocationAndOrg

query with the following options:

a.

Add the

LocName

,

OrgName

,

DepositDate

, and

Weight

fields (in that

order) to the report.

b.

The report should be viewed by

Locations

without any additional grouping

fields.

c.

The data in the report should be sorted in

ascending

order by the

DepositDate

field.

d.

The report should have the

Stepped

layout and the

Portrait

orientation.

e.

Use the name

Deposits by Location

as the title for the report.

f.

In Layout View, reposition the

DepositDate

column to the left

approximately

0.5”

as shown in Figure 9 on the next page.

Save and preview the report to confirm that the first page looks similar to the one

shown in Figure 9 on the next page, then close it.

Figure 9: Portion of the First Page of the Deposits by Location Report

Save and close any open objects in your database. Compact and repair your

database, close it, then exit Access. Follow the directions on the SAM website to

submit your completed project.

pur-new-sol

Related Questions