Homework answers / question archive / New Perspectives Access 2016 | Module 7: SAM Project 1a Healthy Myles Fitness Center CREATING AND ENHANCING REPORTS GETTING STARTED ? Open the file NP_AC16_7a_ FirstLastName _1

New Perspectives Access 2016 | Module 7: SAM Project 1a Healthy Myles Fitness Center CREATING AND ENHANCING REPORTS GETTING STARTED ? Open the file NP_AC16_7a_ FirstLastName _1

MS Access

Share With

---

New Perspectives

Access 2016 | Module 7: SAM Project 1a

Healthy Myles Fitness Center

CREATING AND ENHANCING REPORTS

GETTING STARTED

?

Open the file

NP_AC16_7a_

FirstLastName

_1.accdb

, available for download

from the SAM website.

?

Save the file as

NP_AC16_7a_

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 rptEmployeesAndManagers report, while

completing Steps 1–8.

Figure 1: rptEmployeesAndManagers Report in Design View

1.

Open the

rptEmployeesAndManagers

report in Layout View, and then rename

the report using

Employees and Managers

as the title.

2.

With the

rptEmployeesAndManagers

report still open in Layout View, change the

Format property for the

MonthlySalary

field values to

Currency

.

3.

With the

rptEmployeesAndManagers

report still open in Layout View, add a

subtotal that sums the

MonthlySalary

field values for each location,

and then

add a grand total for all locations.

4.

With the

rptEmployeesAndManagers

report still open in Layout View, make the

following updates:

a.

Change the third column heading to

Employee First Name

, and then

change the fourth column heading to

Employee Last Name

. (

Hint:

Do

not type the period.)

b.

Set the value for the Can Grow property for the

Employee Last Name

field

to

Yes

.

c.

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

are fully visible.

5.

Switch to viewing the

rptEmployeesAndManagers

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 employees that appear in the report based on the

EmployeeID

field.

(

Hint

: Use the COUNT function in the calculated control.)

b.

Use

Number of Employees

as the caption for the label associated with

the text box.

c.

Position the left edge of the text box at approximately the

2"

mark on the

horizontal ruler, and 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.)

d.

Left-align the

Number of Employees

label with the

Manager ID

label (which

is located in the LocationName Header section).

e.

Change the border style for the text box to

Transparent

.

6.

With the

rptEmployeesAndManagers

report still open in Design View, make the

following updates:

a.

In the LocationName Footer section, move the subtotal of the

MonthlySalary

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

MonthlySalary

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

MonthlySalary

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

MonthlySalary

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

rptEmployeesAndManagers

report still open in Design View, r

emove

the alternate back color from all sections of the report.

8.

With the

rptEmployeesAndManagers

report still open in Design View, r

ight

-align

the page number text box with the right edge of the

MonthlySalary

text box.

Save the report, preview both pages of the report, and then close the report.

9.

Create a mailing label report according to the following instructions:

a.

Use the

tblClient

table as the record source.

b.

Use

Avery C2160

labels, and use the

Courier

font, a font size of

12

, the

normal

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

ClientFirst

field, a space, and

the

ClientLast

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

ClientLast

field.

g.

Use

rptClientMailingLabels

as the report title.

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

,

First

,

Last

,

ClientID

,

ClientFirst

,

ClientLast

,

Sessions

,

Amount

.

b.

Remove all the labels and controls from the control layout.

c.

Use the

TrainerID

field as a grouping field, and 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.

Use the

Last

field as a sort field in

ascending

order.

Save the report.

12.

View the

rptBillingByTrainer

report in Design View, and then complete the

following steps:

a.

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

controls until the left edge of the First text box control (in the Details

section) touches the left edge of the report.

b.

Drag the right edge of the report to approximately the

7.5"

mark on the

horizontal ruler.

13.

With the

rptBillingByTrainer

report still open in Design View, make the following

updates:

a.

Move the

First

and

Last

text box controls into the

TrainerID

Header section,

as shown in Figure 5 below.

b.

Delete the

Trainer First

and

Trainer Last

labels in the Page Header section.

c.

Increase the size of the

TrainerID

header slightly, and then move the

First

and

Last

text box controls so that there is one set of grid lines above and

below the controls, as shown in Figure 5.

d.

Draw lines in the

TrainerID

Header section above and below the

First

and

Last

text boxes, as shown in Figure 5. The lines should begin at the left

edge of the report. The ends 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: Header Section of the rptBillingByTrainer Report

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

5"

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

5"

mark. Change the label text to

Grand Total

. (

Hint:

Do not type the

period.)

Figure 6: Footer Section of the rptBillingByTrainer Report

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 is approximately 0.25” from 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.