Illustrated Access 2016 | Module 4: SAM Project 1a

Dives Database

USING REPORTS

GETTING STARTED

?

Open the file

IL_AC16_4a_

FirstLastName

_1.accdb

, available for download from

the SAM website.

?

Save the file as

IL_AC16_4a_

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 file dialog

box, do not type it. Access will add the file extension for you automatically.

?

Open the

_GradingInfoTable

table a

nd 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

Use the Report Wizard to create a report based on the

DiveTrips

table with the

following options:

a.

Include these fields from the

DiveTrips

table in the report:

ID

,

Location

,

City

,

Country

,

StartDate

.

b.

Do not include any additional grouping levels in the report.

c.

Sort the records in the report in ascending order by the

ID

field.

d.

Choose the

Block

layout and the

Portrait

orientation for the report.

e.

Set the title of the report to

Dive Trip Listing

, then preview the report.

Switch to Layout View for the

Dive Trip Listing

report, then make the following

modifications. (

Hint

: If the Field List opens, close it.)

a.

Change the page orientation to

Landscape

.

b.

Resize the StartDate label and text box by dragging the right edge of the

controls to the right to clearly display all information as shown in Figure 1

on the next page.

c.

Change the StartDate label text to

Start Date

.

Your report should match Figure 1 on the next page. Save and close the

Dive

Trip Listing

report.

Figure 1: Dive Trip Listing Report

Use the Label Wizard to create mailing labels for all of the records in the

Divemasters

table using the following options:

d.

Choose the

Avery

labels with the product number

5160

.

e.

Change the font size to

10

but keep the other default font and color

options. (

Hint

: The default options include Arial, Light (font weight), black,

no italic, no underline.)

f.

In the first line of the prototype label, insert the

DiveMasterID

field, a

space, and the

LName

field, then press

Enter

.

g.

In the second line of the prototype label, insert the

Address

field, then

press

Enter

.

h.

In the third line of the prototype label, insert the

City

field, a comma, a

space, the

StateProvince

field, a space, and the

ZipPostalCode

field. The

prototype label should now match Figure 2 below.

i.

Sort the labels using the

LName

field.

j.

Name the report

Divemaster Labels

, then preview the labels. (

Hint

: If

you receive the message “Some data may not be displayed,” click

OK

.)

Save and close the

Divemaster Labels

report.

Figure 2: Prototype Label

2.

Open the

Divemaster Assignments

report in Design View, then narrow the

report by dragging the right edge of the report as far to the left as possible, to

the 10" mark on the horizontal ruler.

3.

With the

Divemaster Assignments

report still open in Design View, change the

text in the Divemaster Assignments label in the Report Header section to

Trips

by Divemaster

, then bold the text in the label.

4.

With the

Divemaster Assignments

report still open in Design View, select the

Participants text box (in the Detail section), then right-align the contents within

the text box.

5.

With the

Divemaster Assignments

report still open in Design View, select all of

the label controls in the Page Header section at the same time and italicize

them.

6.

Click the DiveMasterID Header section bar, then change the Alternate Row Color

to

No Color

in the property sheet. (

Hint

: Use the Alternate Back Color

property’s list arrow.)

7.

With the

Divemaster Assignments

report still open in Design View, open the

Group, Sort, and Total pane, then open the DiveMasterID Footer section.

8.

Change the

StartDate

sort field to

Country

, and then add a second sort order to

the report to sort by

City

. The report should now be grouped by DiveMasterID

and sorted by the

Country

field, then by the

City

field.

9.

Add a text box control to the DiveMasterID Footer section containing the

expression

=Sum([Participants])

to subtotal the number of participants for

each divemaster.

10.

With the

Divemaster Assignments

report still open in Design View, drag the

bottom edge of the Report Footer section down to about 0.5".

11.

Copy the new text box control that you just added to the DiveMasterID Footer

section and paste it in the Report Footer section. (

Hint

: Both the text box and its

accompanying label will be copied and pasted.)

12.

Modify the new label in the DiveMasterID Footer section to read

Subtotal:

.

(

Hint

: Include the colon.)

13.

Modify the label in the Report Footer section to read

Grand Total:

. (

Hint

:

Include the colon.)

14.

With the

Divemaster Assignments

report still open in Design View, align the

right edge of the new =Sum([Participants]) text boxes in both the DiveMasterID

Footer and Report Footer sections with the right edge of Participants text box in

the Detail section.

Align the right edge of the new Subtotal: label in the DiveMasterID Footer section and the

Grand Total: label in the Report Footer section with the Rating text box in the Detail

section.

Add a new text box to the Report Footer section at about the 2" mark on the horizontal

ruler with the following options:

a.

Enter the expression

=Count ([Location])

in the text box. (

Hint

: This

expression will count the total number of records in this report.)

b.

Modify the accompanying label to read

Number of Trips:

. (

Hint

: Include

the colon.)

c.

Switch to Print Preview and preview the report. A portion of the last page

of the report is shown in Figure 3 below. Save and close the

Divemaster

Assignments

report.

Figure 3: Final Divemaster Assignments 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