Chat with us, powered by LiveChat 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 | EssayAbode

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

C:UsersakellerbeeDocumentsSAM DevelopmentDesignPicturesg11731.png 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
SELECT DISTINCTROW * FROM tblEmployee;
SELECT [tblMember].[FirstName], [tblMember].[LastName], [tblBilling].[StartDate], [tblBilling].[Amount], [tblBilling].[Sessions], [tblMember].[MemberID] FROM tblMember INNER JOIN tblBilling ON [tblMember].[MemberID] =[tblBilling].[MemberID];
SELECT [FirstName] & " " & [LastName] AS StudentName, [_GradingReport].ProjectName, "Submission #" & [SubmissionNum] AS SubmissionCt, [_GradingReport].Score, [_GradingReport].MaxScore, "Score is: " & [Score] & " out of " & [MaxScore] AS Grade, [_GradingReportSteps].[StepNumber] & ". " & [Description] AS Step, [_GradingReportSteps].[StepScore] & "/" & [_GradingReportSteps].[StepMaxScore] AS StepGrade, [_GradingReportSteps].ActionName, IIf([ifsuccessful]=True,Null,[ErrorText]) AS Feedback, [_GradingReportSteps].IfSuccessful, [_GradingIcons].GA_Status_Icon, [_GradingReportSteps].StepNumber, [_GradingReportSteps].StepActionOrder, [_GradingReportSteps].ID, [_GradingIcons].SAM_Logo, [_GradingReportSteps].Description, [_GradingReport].EngineVersion FROM _GradingReport, _GradingIcons INNER JOIN _GradingReportSteps ON [_GradingIcons].[IfSuccessful_Status] = [_GradingReportSteps].IfSuccessful;

Related Tags

Academic APA Assignment Business Capstone College Conclusion Course Day Discussion Double Spaced Essay English Finance General Graduate History Information Justify Literature Management Market Masters Math Minimum MLA Nursing Organizational Outline Pages Paper Presentation Questions Questionnaire Reference Response Response School Subject Slides Sources Student Support Times New Roman Title Topics Word Write Writing