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
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 subform
- Create and format a report
- And more…
NOTE: YOU WILL BE USING MICROSOFT ACCESS IT COMES WITH INSTRUCTIONS STEP BY STEP
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
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.
2 |
,
IfSuccessful_Status | GA_Status_Icon | SAM_Logo |
---|---|---|
true | ||
false |
ID | FirstName | LastName | AssignmentGUID | UserID |
---|---|---|---|---|
false | Delvone | Scott | {D1EF0AD6-1875-4871-ADB2-B92BE1B3DC72} | {D1EF0AD6-1875-4871-ADB2-B92BE1B3DC72} |
ID | FirstName | LastName | ProjectName | SubmissionNum | MaxScore | Score | EngineVersion |
---|
ID | StepNumber | Description | IfSuccessful | StepScore | StepMaxScore | ErrorText | ActionName | StepActionOrder |
---|
BillingID | MemberID | TrainerID | StartDate | EndDate | Amount | Sessions |
---|---|---|---|---|---|---|
A80214 | B21845 | 585 | 2/5/16 | 5/3/16 | ¤ 850.00 | 7 |
A80222 | B13298 | 146 | 4/9/16 | 5/2/16 | ¤ 350.00 | 3 |
A80342 | A10025 | 704 | 3/7/16 | 3/28/16 | ¤ 1,000.00 | 10 |
A80399 | A10236 | 774 | 3/16/16 | 4/27/16 | ¤ 600.00 | 6 |
A80565 | B15263 | 585 | 2/1/16 | 2/17/16 | ¤ 600.00 | 6 |
A80610 | B10216 | 585 | 4/1/16 | 5/17/16 | ¤ 1,200.00 | 12 |
A80643 | B10216 | 312 | 2/2/16 | 2/18/16 | ¤ 950.00 | 7 |
A80675 | A10236 | 612 | 3/3/16 | 5/19/16 | ¤ 1,550.00 | 12 |
A84575 | A10025 | 146 | 3/9/16 | 3/21/16 | ¤ 750.00 | 6 |
A84975 | A10236 | 709 | 3/22/16 | 3/24/16 | ¤ 150.00 | 2 |
B80345 | A10488 | 312 | 3/28/16 | 4/18/16 | ¤ 1,000.00 | 10 |
B80671 | B10216 | 585 | 3/1/16 | 3/2/16 | ¤ 250.00 | 2 |
B80699 | A10488 | 585 | 3/8/16 | 3/29/16 | ¤ 450.00 | 4 |
B85654 | B10589 | 146 | 3/2/16 | 3/17/16 | ¤ 1,550.00 | 12 |
B85659 | B15674 | 709 | 4/22/16 | 5/22/16 | ¤ 450.00 | 4 |
B86224 | B22471 | 312 | 6/16/16 | 9/16/16 | ¤ 800.00 | 6 |
B8732 | B22639 | 778 | 1/8/16 | 5/26/16 | ¤ 1,250.00 | 11 |
B8853 | B22731 | 708 | 2/16/16 | 9/22/16 | ¤ 950.00 | 8 |
EmployeeID | LocationID | First | Last | Title |
---|---|---|---|---|
101 | 301 | Lena | Walker | Manager |
104 | 302 | John | Carlson | Manager |
108 | 308 | Steven | Iverson | Manager |
111 | 301 | Lee | Elliott | Receptionist |
114 | 301 | Jackie | Treen | Billing Manager |
117 | 321 | Ken | Bolton | Receptionist |
120 | 321 | Dorothy | Dimitri | Accounts Receivable |
122 | 302 | Karen | Delphard | Accounts Payable |
126 | 301 | Peter | Nowak | Accounts Receivable |
131 | 318 | Ruth | Collins | Receptionist |
137 | 301 | Iris | Rodriguez | Accounts Payable |
142 | 318 | Sam | Bukowski | Accounts Receivable |
148 | 308 | Donna | Smith | Accounts Payable |
153 | 302 | Barbara | Loft | Receptionist |
155 | 308 | Rick | Vitale | Receptionist |
LocationID | LocationName | LocationOpened | Address | City | State | ZipCode | Phone |
---|---|---|---|---|---|---|---|
301 | Elmwood | 5/2/14 | 1400 Elmwood Ave. | Buffalo | NY | 14216 | 716-555-1212 |
302 | Riverside | 6/8/15 | 733 Hertel Ave. | Buffalo | NY | 14207 | 716-555-8315 |
308 | Sheridan | 1/5/15 | 5200 Sheridan Dr. | Buffalo | NY | 14221 | 716-555-7621 |
318 | Eastside | 2/7/15 | 883 Jefferson Ave. | Buffalo | NY | 14204 | 716-555-4455 |
321 | Genesee | 7/2/15 | 229 W Genesee St. | Buffalo | NY | 14202 | 716-555-9427 |
MemberID | FirstName | LastName | Address | City | State | Zip | Phone | BirthDate | Gender |
---|---|---|---|---|---|---|---|---|---|
A10025 | Ron | Ayers | 1310 Hazen St. SE | Grand Rapids | MI | 49507 | 616-555-4815 | 12/15/64 | M |
A10236 | John | McLaughlin | 1830 Breton Rd. SE | Grand Rapids | MI | 49506 | 616-555-2412 | 1/6/92 | M |
A10488 | Anna | Clark | 3665 28th St. SE | Grand Rapids | MI | 49512 | 616-555-9912 | 9/3/94 | F |
A10522 | Dave | Krebs | 2171 Holton Ct. NW | Walker | MI | 49544 | 616-555-2156 | 4/8/74 | M |
B10216 | Maria | Scott | 270 Ann St. NW | Grand Rapids | MI | 49544 | 616-555-9855 | 2/28/71 | F |
B10589 | Sandra | Atchinson | 3251 Deposit Dr. NE | Grand Rapids | MI | 49546 | 616-555-9034 | 7/1/76 | F |
B13298 | Bill | Ellison | 3063 Lake Eastbrook Blvd. SE | Grand Rapids | MI | 49512 | 716-555-3664 | 5/3/73 | M |
B15263 | Calvin | Welmers | 3524 28th St. SE | Grand Rapids | MI | 49512 | 616-555-2598 | 9/5/83 | M |
B15674 | Michelle | Ball | 2419 Eastern Ave. SE | Grand Rapids | MI | 49507 | 616-555-5612 | 2/2/87 | F |
B21845 | Anthony | Geisen | 1977 E Beltline Ave. NE | Grand Rapids | MI | 49525 | 616-555-2367 | 2/7/89 | M |
B22471 | Ashley | Miller | 550 Lakeside Dr. SE | Grand Rapids | MI | 49506 | 616-555-3299 | 7/18/06 | F |
B22639 | Amy | Ryke | 956 Fulton St. E | Grand Rapids | MI | 49503 | 616-555-8923 | 11/21/97 | F |
B22731 | Joel | Watkins | 3633 Eastern Ave. SE | Grand Rapids | MI | 49508 | 616-555-6529 | 9/5/01 | M |
TrainerID | LocationID | FirstName | LastName | Specialty | Certification | HireDate | Interests | Minors |
---|---|---|---|---|---|---|---|---|
146 | 301 | Jennifer | Young | Personal Trainer | RYT | 3/19/14 | nutrition, strength training, motivation | true |
312 | 308 | Randy | Lee | Strength Training | RYT | 9/27/13 | yoga, nutrition | true |
454 | 301 | Josh | Martinez | Sports Medicine | NATA | 11/9/15 | martial arts, strength training | false |
585 | 302 | Cam | Williams | Sports Medicine | NASM | 6/10/14 | nutrition, martial arts | false |
612 | 318 | Raj | Patel | Personal Trainer | NSCA-CPT | 1/4/16 | yoga, dance fit, nutrition | true |
693 | 321 | Sandy | Larson | Personal Trainer | NSCA-CPT | 2/11/15 | martial arts, nutrition, yoga | false |
704 | 302 | Melissa | Rogers | Strength Training | NSCA-CSCS | 6/15/15 | sports medicine, yoga | false |
708 | 301 | Carson | Garner | Personal Trainer | RYT | 2/25/13 | nutrition, yoga, swimming | false |
709 | 321 | Richard | Sanderson | Sports Medicine | NASM | 6/1/16 | strength training, swimming | false |
774 | 308 | Sally | Andrews | Personal Trainer | NSCA-CPT | 4/12/14 | nutrition, sports medicine, strength training | false |
778 | 318 | Nick | Collins | Martial Arts | Black Belt | 1/29/16 | martial arts | true |
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.