Homework answers / question archive / Shelly Cashman Access 2016 | Module 6: SAM Project 1b All Around Outside Maintenance ADVANCED REPORT TECHNIQUES GETTING STARTED   Open the file SC_AC16_6b_ FirstLastName _1

Shelly Cashman Access 2016 | Module 6: SAM Project 1b All Around Outside Maintenance ADVANCED REPORT TECHNIQUES GETTING STARTED   Open the file SC_AC16_6b_ FirstLastName _1

MS Access

Share With

---

Shelly Cashman Access 2016 | Module 6: SAM Project 1b

All Around Outside Maintenance

ADVANCED REPORT TECHNIQUES

GETTING STARTED

 

Open the file

SC_AC16_6b_

FirstLastName

_1.accdb

, available for download

from the SAM website.

 

Save the file as

SC_AC16_6b_

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.

 

To complete this Project, you will also need to download and save the following

support file from the SAM website:

o

Support_SC_AC16_6b_States.txt

 

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.

Import the data from the file

Support_SC_AC16_6b_States.txt

, available for

download from the SAM website, and then append the records to the

State

table. The text file is a delimited file with a comma separating the two fields. Do

not create a new table, and do not save the import steps.

2.

Open the

Basic Property List

report in Design View, and then complete the

following tasks:

a.

Add the current date and time to the Report Header section.

b.

In the Date and Time dialog box, use the option button to select the

second date format (e.g., 01-Aug-18) and the second time format (e.g.,

4:18 PM).

c.

Resize the report so that the right boundary of the report is approximately

at the

8.0"

mark on the horizontal ruler.

Save the report without closing it.

3.

With the

Basic Property List

report still open in Design View, complete the

following tasks:

a.

Select the State Header, and then display the Property Sheet for this

section.

b.

Change the Repeat Section property to

Yes

.

c.

Change the Force New Page property to

Before Section

.

Save and close the report.

4.

Modify the

Clients by Manager Report

to match Figure 1 below by completing

the following tasks:

a.

Open the

Clients by Manager Report

in Design View.

b.

Open the field list for the report.

c.

Add the

LastName

field to the ManagerID Header section of the report in

the postion shown in Figure 1. (

Hint

: You may need to reposition and resize

the

LastName

control and/or its label after inserting it into the report.)

5.

With the

Clients by Manager Repor

t still open in Design View, complete the

following tasks:

a.

Select the

ClientNumber

,

ClientName

,

AmountPaid

, and

Balance

controls

simultaneously.

b.

Group these controls.

c.

Resize the controls by dragging their right edge to the

4"

mark on the

ruler, as shown in Figure 1.

d.

Bold

these controls.

Save and close the

Clients by Manager Report

.

Figure 1: Clients by Manager Report – Design View

6.

Modify the

Client Financial Report

to match Figure 2 below by completing the

following tasks:

a.

Open the

Client Financial Report

in Design View.

b.

Change the

Text5

label in the Page Header section to

Total Amount

.

(

Hint

: Do not include the period.)

c.

In the Detail section, format the text box control containing the =[Amount

Paid]+[Balance] calculation so that it displays using the

Currency

number

format with two decimal places as shown in Figure 2.

Save and close the

Client Financial Report

.

Figure 2: Client Financial Report – Report View

7.

Open the

Manager Phone List

report in Design View, and then change the

Alternate Back Color property for the Detail section to

No Color

. Save and close

the report.

8.

Open the

Manager Master Report

in Design View, and then use the

Subform/Subreport Wizard to add a subreport with the following options:

a.

Add the subreport below the

ClientName

label in the Detail section, at

approximately the

1"

mark on the vertical and horizontal rulers.

b.

Use the

Property

table for the subreport.

c.

Select the

PropertyID

,

Address

,

City

, and

State

fields in that order from the

Property

table to add to the subreport.

d.

Ensure that the “Choose from a list.” option button is selected, and then

accept the default link (“Show Property for each record in Managers and

Clients using ClientNumber”) for the subreport.

e.

Save the subreport as

Property subreport

(which is the default name).

(

Hint

: The width of the subreport may expand the width of the report. Do

not manually adjust the width.)

f.

Delete the label associated with the subreport.

Save the

Manager Master Report

, switch to Report View, confirm your report

matches Figure 3 below, and then close the report.

Figure 3: Manager Master Report – Report View

9.

Open the

Clients by Type Report

in Design View, and then modify the report to

match Figure 4 on the following page by completing the tasks below:

a.

Resize the width of the report as shown in Figure 4. (

Hint

: The right border

of the report is at the

7"

mark on the horizontal ruler.)

b.

Move the

Phone

label and control so that they appear under the

PostalCode

control in the approximate location shown in Figure 4. (

Hint

:

Align the top of the label and control with the

2.0"

mark on the vertical

ruler.)

c.

Select the

AmountPaid

,

Balance

,

TotalAmount

, and

ManagerID

controls

simultaneously. Do not select the labels.

d.

Align the controls to the right as shown in Figure 4.

Save and close the report.

Figure 4: Clients by Type Report – Design View

10.

Open the

Manager Salary Report

in Design View, and then modify the report by

completing the following tasks:

a.

Select all labels in the Page Header section.

b.

Bold

the labels.

c.

Select the

LastName

control in the Detail section, and then change the

Can Grow property to

Yes

.

d.

Resize the Detail section of the report by dragging the lower boundary of

the section to just below the controls in the report, as shown in Figure 5 on

the following page.

Save and close the report.

Figure 5: Manager Salary Report – Design View

11.

Modify the

Manager Client Balance Report

to match Figure 6 on the following

page by performing the tasks below:

a.

Open the

Manager Client Balance Report

in Design View.

b.

Use the Group, Sort, and Total Pane to add a footer section to the

Group

on ManagerID

section.

c.

Add a text box control in the ManagerID Footer.

d.

Convert the text box control into a calculated control that sums the

Balance

field. (

Hint

: Your control should contain the formula

=Sum([Balance])

).

e.

Format the control so that it displays using the

Currency

number format

with two decimal places.

f.

Change the label to

Client Total

. (

Hint

: Do not include the period.)

g.

Reposition the control and its label to the approximate location shown in

Figure 6.

Save and close the report.

Shelly Cashman

Access 2016 | Module 6: SAM Project 1b

Figure 6: Manager Client Balance Report – Design View

12.

Create a new blank report in Design View, and then complete the following

tasks:

a.

Open the Property Sheet for the report, and then set the Record Source for

the report to the

State

table.

b.

Save the report with the name

State Report

, but do not close the report.

(

Hint

: Do not include the comma in the report name.)

c.

Add the fields

State

and

StateName

to the report, and then reposition

them to the approximate location shown in Figure 7 on the following page.

d.

Add the title

State Report

to the Report Header section.

e.

Add page numbers to the report at the

Top of Page (Header

) position,

using the

Page N of M

format and

Right

alignment.

Save and close the report.

Figure 7: State Report – Design View

13.

Open the

Properties by State Report

in Design View. Group the report by the

State

field, and then sort the report by the

PropertyID

field in

ascending

order.

Do not add any additional grouping or sorting options to the report. Save and

close the

Properties by State Report

. (

Hint

: This report does not contain any

fields.)

14.

Modify the

Financial Totals Report

to match Figure 8 on the following page by

completing the tasks below:

a.

Open the

Financial Totals Report

in Design View.

b.

Add a text box control in the Report Footer section.

c.

Convert the text box control into a calculated control that sums the

TotalAmount

field. (

Hint

: Your control should contain the formula

=Sum([TotalAmount])

).

d.

Format the control so that it displays using the

Currency

number format

with zero decimal places.

e.

Change the label to

Total

. (

Hint

: Do not include the period.)

f.

Reposition the control and its label to the approximate location shown in

Figure 8. (

Hint

: The left edge of the text box should be located at the

5"

mark on the horizontal ruler.)

Save and close the report.

Figure 8: Financial Totals Report – Design View

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.