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
Subject:MS AccessPrice: Bought3
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.
