Homework answers / question archive / New Perspectives Access 2016 | Module 7: SAM Project 1b GRMH Recreation Centers CREATING AND ENHANCING REPORTS GETTING STARTED ? Open the file NP_AC16_7b_ FirstLastName _1

New Perspectives Access 2016 | Module 7: SAM Project 1b GRMH Recreation Centers CREATING AND ENHANCING REPORTS GETTING STARTED ? Open the file NP_AC16_7b_ FirstLastName _1

MS Access

Share With

---

New Perspectives Access 2016 | Module 7: SAM Project 1b

GRMH Recreation Centers

CREATING AND ENHANCING REPORTS

GETTING STARTED

?

Open the file

NP_AC16_7b_

FirstLastName

_1.accdb

, available for download

from the SAM website.

?

Save the file as

NP_AC16_7b_

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

Refer to Figure 1 below, which shows the last page of the

rptLocationsAndBilling

report,

while completing Steps 1–8.

Figure 1: Design View of the rptLocationsAndBilling Report

1.

Open the

rptLocationsAndBilling

report in Layout View, and then c

hange

the

report title to

Locations and Billing

. (

Hint:

Do not type the period.

2.

With the

rptLocationsAndBilling

report still open in Layout View, change the

Format property for the

Amount

field values to

Currency

.

3.

With the

rptLocationsAndBilling

report still open in Layout View, add a subtotal

that sums the

Amount

field values for each location, and then add a grand total

for all locations.

4.

With the

rptLocationsAndBilling

report still open in Layout View, make the

following updates:

a.

Change the first column heading to

Member Last Name

and the second

column heading to

Member First Name

. (

Hint:

Do not type the period.)

b.

Set the value for the Can Grow property for the

Member Last Name

field to

Yes

.

c.

Resize all of the column headings (and their text boxes) so that all values

are fully visible. (

Hint:

Values may already be visible.)

5.

Switch to viewing the

rptLocationsAndBilling

report in Design View, and then

update the report as described below:

a.

Use a text box control in the Report Footer section to display the number

of records that appear in the report based on the

LastName

field. (

Hint

:

Use the COUNT function in the calculated control.)

b.

Position the left edge of the text box at approximately the

1.75"

mark on

the horizontal ruler, and then position the top edge of the text box

approximately in the second row of grid dots in the Report Footer section.

(

Hint

: You may need to slightly increase the height of the Report Footer

section.)

c.

Left-align

the label with the

Location Name

label (which is located in the

LocationName Header section).

d.

Change the border style for the text box to

Transparent

.

e.

Change the label’s caption to

Number of Invoices

. (

Hint:

Do not type the

period.)

6.

With the

rptLocationsAndBilling

report still open in Design View, make the

following updates:

a.

In the LocationName Footer section, move the subtotal of the

Amount

text

box control down so that its top edge is approximately in the second row

of grid dots in this section. (

Hint

: You will need to slightly increase the

height of the LocationName Footer section.)

b.

Draw a line above the subtotal of the

Amount

text box control in the

LocationName Footer section, using Figure 1 as a guide.

c.

In the Report Footer section, move the grand total of the

Amount

text box

control down so that its top edge is approximately in the second row of

grid dots in this section. (

Hint

: You may need to slightly increase the

height of the Report Footer section.)

d.

Draw a line above the grand total of the

Amount

text box control in the

Report Footer section, using Figure 1 as a guide.

e.

Align the lines and the text box controls below them on their right sides,

and make sure that their lengths are approximately equal.

7.

With the

rptLocationsAndBilling

report still open in Design View, r

emove

the

alternate back color from all sections of the report.

8.

With the

rptLocationsAndBilling

report still open in Design View,

r

ight

-align

the

page number text box with the right edge of the

Amount

text box. Save the

report, preview both pages of the report in Print Preview, and then close the

report.

9.

Create a mailing label report according to the following instructions:

a.

Use the

tblMember

table as the record source.

b.

Use

Avery C2160

labels, and use the

Georgia

font, the

11 pt.

font size,

the

Semi-bold

font weight, standard

Black

(1

st

column, 6

th

row of the

Basic Color Palette) as the text color, and no special font styles.

c.

In the first line of the prototype label, add the

FirstName

field, a space,

and the

LastName

field.

d.

In the second line of the prototype label, add the

Address

field.

e.

In the third line of the prototype label, add the

City

field, a comma, a

space, the

State

field, a space, and the

Zip

field. Confirm the prototype

label matches Figure 2 below.

f.

Sort the report by the

City

field and then by the

LastName

field.

g.

Use

rptMemberMailingLabels

as the name of the report.

Save and close the report.

Figure 2: Prototype Label

While creating the custom report in Steps 10–16, refer to Figure 3 on the following page

(which shows the first page of the rptBillingByTrainer report) and Figure 4 on page 5

(which shows the second page of the rptBillingByTrainer report).

Figure 3: First Page of the rptBillingByTrainer Report

Figure 4: Second Page of the rptBillingByTrainer Report

10.

Create a blank report, and then set the report’s record source to the

qryBilling

query. Save the report as

rptBillingByTrainer

without closing the report.

11.

In Layout View, make the following changes to the

rptBillingByTrainer

report:

a.

Add all the fields from the

qryBilling

query to the report in the following

order:

TrainerID

,

tblTrainer.FirstName

,

tblTrainer.LastName

,

MemberID

,

tblMember.FirstName

,

tblMember.LastName

,

Sessions

,

Amount

.

b.

Remove all the labels and controls from the control layout.

c.

Use the

TrainerID

field as a grouping field, and then set the Keep Together

property so that the whole group is together on one page.

d.

Add a subtotal for each trainer and a grand total for all trainers using the

Amount

field and the SUM function. (

Hint

: Remember to check the

Show

subtotal in group

footer

option so that the subtotals are visible in the

report.)

e.

Delete the

TrainerID

control and label from the report.

f.

Sort the report by the

tblMember

.

LastName

field in

ascending

order.

Save the report.

12.

View the

rptBillingByTrainer

report in Design View, and then complete the

following steps:

a.

Select the Amount control and label, the subtotal control in the TrainerID

Footer section, and the grand total control in the Report Footer section.

Reduce their width to approximately

0.8”

by dragging their right edges to

the left using the mouse.

b.

Select all controls and labels in the report, and, as a group, move the

labels and controls until the left edge of the

tblTrainer

.

FirstName

text box

control (in the Detail section) touches the left edge of the report. (

Hint:

The left edge of the

tblTrainer.FirstName

control should be at the

0”

mark

on the horizontal ruler).

c.

Drag the right edge of the report to approximately the

7.75"

mark on the

horizontal ruler.

13.

With the

rptBillingByTrainer

report still open in Design View, make the following

updates:

a.

Move the

tblTrainer.FirstName

and

tblTrainer.LastName

text box controls

into the TrainerID Header section, as shown in Figure 5 on the following

page.

b.

Delete the

tblTrainer.FirstName

and

tbl.Trainer.LastName

labels in the

Page Header section.

c.

Increase the size of the TrainerID Header slightly, and then move the

tblTrainer.FirstName

and

tblTrainer.LastName

text box controls so that

there is one set of grid lines above and below the controls (see Figure 5).

d.

Draw lines in the TrainerID Header section above and below the

tblTrainer.FirstName

and

tbl.Trainer.LastName

text boxes as shown in

Figure 5. The lines should begin at the left edge of the report. The end of

the lines should align with the right edge of the

Amount

label in the Page

Header section.

Save the report without closing it.

Figure 5: rptBillingByTrainer Report in Design View.

14.

With the

rptBillingByTrainer

report still open in Design View, make the following

updates:

a.

Move the subtotal control in the TrainerID Footer section down so that

there is one row of gridlines above the control. (

Hint

: This will resize the

TrainerID Footer section.)

b.

Move the grand total control in the Report Footer section down so that

there is one row of gridlines above the control. (

Hint

: This will resize the

Report Footer section.)

c.

Add a line above the subtotal control (in the TrainerID Footer section) and

above the grand total control (in the Report Footer section) in the

approximate location shown in Figure 5 above. The controls and the lines

all should have the same length.

d.

Add a label to the left of the subtotal control in the TrainerID Footer

section, so that the left edge of the label is located approximately at the

6"

mark. Change the label text to

Trainer Total

. (

Hint

: Do not type the

period.)

e.

Add a label to the left of the grand total control in the Report Footer

section, so that the left edge of the label is located approximately at the

6"

mark. Change the label text to

Grand Total

. (

Hint

: Do not type the

period.)

15.

With the

rptBillingByTrainer

report still open in Design View, make the following

updates:

a.

For all sections of the report, set the label and text box border style to

Transparent

.

b.

Remove the alternate back color from all sections of the report.

Save the report without closing it.

16.

With the

rptBillingByTrainer

report still open in Design View, make the following

updates:

a.

Add the title

Billing by Trainer

to the Report Header section, and then

remove the control layout from the title. Move the title label so that the

left edge of the control touches the left edge of the report.

b.

Center-align

the report title.

c.

Add the date to the Report Header section, using the format

3/21/2019

.

Do not include the time. Move the control so that the right edge of the

date text box aligns with the right edge of the

Amount

label in the Page

Header section.

d.

Add the page number with the format

Page N of M

to the Page Footer

section. Do not change the default center alignment, and do not uncheck

the check box associated with showing page numbers on the first page.

e.

Align the right side of the page number text box with the right side of the

Amount

label in the Page Header section.

Save the report, preview both pages of the report in Print Preview, and then

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