New Perspectives Access 2016 | Module 4: SAM Project 1a
Healthy Myles Fitness Center
USING FORMS AND REPORTS TO VIEW DATA
GETTING STARTED
•
Open the file NP_AC16_4a_FirstLastName_1.accdb, available for download
from the SAM website.
•
Save the file as NP_AC16_4a_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
1.
You are employed at Healthy Myles Fitness Center and are in charge of
consolidating the company’s records and other important information into a
database. You have already set up the basic structure of the database, created
relationships between tables, and created queries. You will now work with
forms and reports to enter and view data.
Use the Form tool to create a form based on the tblTrainer table, and then save
the form as frmTrainerList. Close the frmTrainerList form.
2.
Open the frmEmployeeList form in Layout view and make the following
changes:
a.
Apply the Facet theme to the frmEmployeeList form only.
b.
Change the form title to Employee List.
c.
Change the font color of the form title to Dark Green, Accent 2.
d.
Save the form, and then switch to viewing the frmEmployeeList form in
Form View.
3.
In Form View, navigate to the fifth record (with the Employee ID field value
114) in the frmEmployeeList form, and then change the Employee Last field
value to Summers. Navigate to the last record (with the Employee ID field
value 155), and then change the Title field value to Billing Manager. Save and
close the form.
4.
Use the Form Wizard to create a form based on the tblBilling table. Include all
fields in the form, use the Columnar layout, and specify the title
frmBillingData.
New Perspectives Access 2016 | Module 4: SAM Project 1a
a.
Switch to Layout View, and then edit the form title in the frmBillingData
form so that it appears as Billing Data (two words). Save the form.
5.
Switch to Form View, and then use the Find command to find the record with
the Billing ID field value A80565 in the frmBillingData form. Change the
Sessions to 8 and the Amount to $800.
6.
Using the data shown in Figure 1 below, add a new record to the frmBillingData
form, and then close the form.
Figure 1: New Record for the frmBillingData Form
7.
Use the Form Wizard to create a form containing a main form and a subform as
described below:
a.
Select the TrainerID, FirstName, and LastName fields from the tblTrainer
table for the main form.
b.
Select the ClientID, StartDate, and EndDate fields from the tblBilling table.
c.
Choose the option to view the data by tblTrainer.
d.
Select the Datasheet layout for the subform.
e.
Specify the title frmTrainerBilling for the main form and
frmBillingSubform for the subform.
New Perspectives Access 2016 | Module 4: SAM Project 1a
8.
Switch to Layout View, change the title in the main form to Trainers and
Billing, then use the mouse to resize the subform to approximately 4.5” wide
(or wide enough to display the full EndDate column). Your form should now
look like Figure 2 below.
Figure 2: frmTrainerBilling Form
9.
In Form View, use the Find command to find the record with the Trainer ID field
value 585 in the frmTrainerBilling form, and then change the FirstName field
value to Cameron. Save and close the form.
New Perspectives Access 2016 | Module 4: SAM Project 1a
10.
Use the Report Wizard to create a report based on the tblTrainer table and the
related tblBilling table, as described below:
a.
Select the TrainerID, FirstName, LastName, and Certification fields from
the tblTrainer table.
b.
Select all fields from the tblBilling table except for the TrainerID field.
c.
Choose the option to view the data by tblTrainer, and do not select any
additional grouping levels for the report.
d.
Sort the records in ascending order by BillingID.
e.
Select the Outline layout and Landscape orientation for the report.
f.
Specify the report title rptTrainerBilling.
11.
Switch to Layout View, and then change the report title for the rptTrainerBilling
report to Trainer Billing (two words). Save and close the report.
12.
Open the rptClientBilling report in Layout View and complete the following
steps:
a.
Apply the Integral theme to the rptClientBilling report only.
b.
Increase the width of the StartDate controls to 1.2".
c.
Move the StartDate controls so that the right edge is aligned with the
StartDate field title. Move the Amount controls so that the right edge is
aligned with the Amount field title.
d.
Change the Sessions field title to right alignment.
e.
Scroll to the bottom of the report, and then use an arrow key to move the
page number control to the right so that its right edge aligns with the
right edge of the Sessions field in the report. Save the report.
f.
Use conditional formatting in the rptClientBilling report to format the
Sessions field values that are greater than or equal to 7 in a bold, red
font, as shown in Figure 3 on the next page. Display the report in Print
Preview and review its pages, and then save and close the report.
New Perspectives Access 2016 | Module 4: SAM Project 1a
Figure 3: rptClientBilling 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.
Purchase answer to see full
attachment