Illustrated Access 2016 | Module 6: SAM Project 1a

Dive Trips Database

IMPROVING QUERIES

GETTING STARTED

Open the file

IL_AC16_6a_

FirstLastName

_1.accdb

, available for download

``from the SAM website.

Save the file as

IL_AC16_6a_

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.

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

DiveTrips

table with the

following options:

a.

Add the

Country

and

Participants

fields from

DiveTrips

table (in that order)

to your query.

b.

Add the

Total

row to the query and sum the

Participants

field.

c.

Save the query with the name

TotalsByCountry

and run the query. (

Hint

:

The

Participants

field will be renamed

SumOfParticipants

when you run

this query.)

2.

Switch to viewing the

TotalsByCountry

query in Datasheet View, add the

Total

row to the datasheet, and sum the

SumOfParticipants

field in the datasheet, as

shown in Figure 1 on the next page. Save and close the

TotalsByCountry

query.

Figure 1: TotalsByCountry Query

3.

Right-click the

DivesByStartDate

query, then copy and paste it using

March9AndHighRatings

as the name.

4.

Open the

March9AndHighRatings

query in Design View, add criteria to select

only those records with the

StartDate

field value 3/9/17 and a

Rating

field value

greater than or equal to 4. (

Hint

: Records returned by this query should meet

both criteria.) Switch to Datasheet View, confirm that your query returns the

records shown in Figure 2 on the next page, then save and close the query.

Figure 2: March9AndHighRatings Query

5.

Right-click the

DivesByStartDate

query, then copy and paste it using

KeyOrLarge

as the name. Open the

KeyOrLarge

query in Design View and

make the following updates:

a.

Add criteria to select records that contain the text

Key

anywhere in the

City

field or that contain a

Participants

field value greater than 13. (

Hint

:

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

b.

Add two sort orders to sort the records first in

ascending

order by the

LName

field and then in

ascending

order by the

StartDate

field.

Switch to viewing the

KeyOrLarge

query in Datasheet View, confirm the query

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

Figure 3: KeyOrLarge Query

6.

Create a new query in Design View based on the

Divemasters

and

DiveTrips

tables with the following options:

a.

Use the

LName

field from the

Divemasters

table and the

Location

,

City

,

and

Country

fields from

DiveTrips

table (in that order) in your query.

b.

Add criteria using the In operator to select all records in which the

Country

field is equal to “Australia” or “Fiji”.

Save the query with the name

AustraliaFiji

, switch to Datasheet View to

confirm the query looks similar to the one shown in Figure 4 below, then close

the query.

Figure 4: AustraliaFiji Query

7.

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

DivesByStartDate

query, as described below:

a.

Select the

Country

field for the Row headings.

b.

Select the

LName

field for the column headings.

c.

Select

Participants

as the calculated field.

d.

Select

Sum

as the function.

e.

Enter

Diver_Crosstab

as the query name.

Confirm the query appears as shown in Figure 5 on the next page, then close

the query. (

Hint

: Figure 5 only shows a portion of the query. It extends to the

right beyond the

Lincoln

column.)

Figure 5: Diver_Crosstab Query

1.

Open the

Revenue

query in Design View, then create a calculated field to the

right of the

TripPrice

field named

Total

that multiplies the

TripPrice

field by the

Participants

field. (

Hint

: The calculated control should be structured as

[TripPrice]*[Participants]

.) Save the

Revenue

query, display it in Datasheet

View (the first few records of which are shown in Figure 6 on the next page),

then close the

Revenue

query.

Figure 6: Revenue Query

8.

View the

DivemasterBirthdays

query in Design View, then create a calculated

field to the right of the

Birthday

field named

DivemasterAge

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 current age of each

divemaster:

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

Save the

DivemasterBirthdays

query, display it in Datasheet View, as shown in

Figure 7 on the next page, then close the query. (

Hint

:

Depending on the date

you complete this assignment, the values in the

DivemasterAge

column in your

query may not match those in Figure 7.)

Figure 7: DivemasterBirthdays Query

9.

Open the

ParticipantsByMaster

query in Design View and add the

Total

row to

the query. The query should be grouped by the

Lname

field and should sum the

values in the

Participants

field. Save and run the query, then close it.

10.

Open the

USADives

query in Design View and apply a sort to the query in

ascending

order by the

State

field, an

ascending

order sort by the

City

field,

and an

ascending

order sort by the

StartDate

field. Save and run the query,

then close it.

11.

Create a crosstab query in Design View based on the

DivesByStartDate

query

with the following options:

a.

Add the

Country

,

Rating

, and

Participants

fields to the query (in that

order).

b.

Use the

Country

field as the row heading.

c.

Use the

Rating

field as the column heading.

d.

Use the

Participants

field as the value for the crosstab query, using the

SUM function for the

Total

row calculation for this field.

a.

Save the query with the name

CountryRating_Crosstab

and run it.

Confirm the query looks similar to the one shown as shown in Figure 8 on

the next page, then close the query.

Figure 8: CountryRating_Crosstab Query

2.

Use the Report Wizard to create a report based on the

DivesByLocation

query

with the following options:

a.

Add the

Location

,

Country

,

City

, and

StartDate

fields (in that order) to the

report.

b.

The report should be viewed by

Country

without any additional grouping

fields.

c.

The data in the report should be sorted in

ascending

order by the

StartDate

field.

d.

The report should have the

Stepped

layout and

Portrait

orientation.

e.

Use

Dives by Date

as the title for the report.

f.

In Layout View, resize the

StartDate

column so that the data is completely

visible and reposition it to the left approximately 0.5” as shown in Figure 9

on the next page.

Save and preview the Dives by Date 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 Dives by Date 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