23 Jun This challenge will give you chance to test your knowledge before being trained. You will be asked to: Create and format a form Create and format a form that uses a

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

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

GRMH Recreation Centers

Using FORM and Reports to View Data

GETTING STARTED

· Open the file NP_AC16_4b_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as NP_AC16_4b_FirstLastName_2.accdb by changing the “1” to a “2”.

· 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

You are an employee at the GRMH Recreation Centers in Grand Rapids, MI. Your supervisor, Samantha Cooper, had decided to create an Access database to more easily track information such as billing data, and has asked for your help creating it. In this project, you’ll work with forms and reports to allow you to easily update 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.

In Layout View, make the following changes to the frmEmployeeList form:

a. Apply the Office theme to the frmEmployeeList form only.

b. Use Employee List as form title.

c. Change the font color of the form title to Red (2nd column, 7th row of the Standard Colors palette).

d. Save the form, and then switch to view the frmEmployeeList form in Form View.

In Form View, navigate to the seventh record (with the Employee ID field value 120) in the frmEmployeeList form, and then change the Employee Last field value to Doyle. 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.

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. (Hint: Do not type the period.) Switch to Layout view, and edit the form title in the frmBillingData form so that it appears as Billing Data (two words). Save the form.

Switch the frmBillingData form to Form View, and then use the Find command to find the record with the BillingID field value A80565 in the frmBillingData form. Change the Sessions to 8 and the Amount to $800.

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

Use the Form Wizard to create a form containing a main form and a subform as described below:

e. Select all fields from the tblTrainer table for the main form.

f. Select the MemberID, StartDate, and EndDate fields from the tblBilling table.

g. Choose the option to view the data by tblTrainer.

h. Select the Datasheet layout for the subform.

i. Specify the title frmTrainerBilling for the main form and frmBillingSubform for the subform.

Switch to Layout View, and then change the title in the main form to Trainers and Billing. Your form should now match Figure 2 below.

Figure 2: frmTrainerBilling Form

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 Certification field value to NATA. Save and close the form.

Use the Report Wizard to create a report based on the tblTrainer table and the related tblLocation table, as described below:

j. Select the LocationID, LocationName, Address, and City fields from the tblLocation table.

k. Select all fields from the tblTrainer table except for the LocationID field.

l. Choose the option to view the data by tblLocation, and do not select any additional grouping levels for the report.

m. Sort the records in ascending order by the LastName field.

n. Select the Outline layout and Landscape orientation for the report.

o. Use rptTrainerLocation as report title.

Switch to Layout View, and then change the report title for the rptTrainerLocation report to Trainer Location (two words). (Hint: Sometimes the report is generated such that the date fields are not wide enough to display the full dates. In this case, placeholder symbols are displayed using the # symbol.) Close the report.

Open the rptMemberBilling report in Layout View and complete the following steps:

p. Apply the Office theme to the rptMemberBilling report only.

q. Increase the width of the StartDate controls to 1.2".

r. 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.

s. Change the Sessions field title to right alignment.

t. 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.

u. Use conditional formatting in the rptMemberBilling report to format the Sessions field values that are greater than or equal to 8 in a bold, green (6th column, 7th row of the Standard Colors palette) font formatting, 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.

Figure 3: rptMemberBilling Report

,

Related Tags