New Perspectives Access 2013Tutorial 4: SAM Project 1a Physical TherapySpecialists, P.C.USING QUERIES AND REPORTS TO VIEW DATA CT NAMEPROJECT DESCRIPTIONJennifer Christie wants to streamline the data-entry process at Physical Therapy Specialistsby creating forms that her staff can use to update and view data about employees andpatient billing. She also wants to create a report to display patient billing data. You’ll createtwo forms and a report to help Jennifer with her requests. GETTING STARTEDDownload the following file from the SAM website:o NP_Access2013_T4_P1a_FirstLastName_1.accdb Open the file you just downloaded and save it with the name:oo NP_Access2013_T4_P1a_FirstLastName_2.accdbHint: If you do not see the .accdb file extension in the Save file dialog box, donot type it. Access will add the file extension for you automatically. To complete this Project, you will also need to download and save the followingsupport file from the SAM website:o support_NP_A13_T4_P1a_pst.jpg Open the _GradingInfoTable table and ensure that your first and last name isdisplayed 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 STEPS1. Use the Form tool to create a form based on the Employee table, and thensave the form as EmployeeList. 2. In Layout View, make the following changes to the EmployeeList form:a. Remove the form title from the control layout, and then change theform title to Employee List.b. Apply the Integral theme to the EmployeeList form only. (Hint: Becertain that you are only applying this theme to the EmployeeListform.)c. Save the form. Copyright © 2014 Cengage Learning©. All Rights Reserved.Microsoft product screenshots used with permission from Microsoft Corporation.Reserved.Microsoft New Perspectives Access 2013 | Tutorial 4: SAM Project 1aIn Form View, use the EmployeeList form to navigate to the third record (withEmployee ID 108), and then change the Employee Last field value toSummers. Navigate to the last record (with Employee ID 199), and thenchange the Title field value to Billing Manager. Close the form. 4. Use the Form Wizard to create a form based on the Billing table. Include allfields in the form, use the Columnar layout, and specify the title BillingData. 5. Switch to Layout View and edit the form title in the BillingData form so that itappears as Billing Data (two words). Change the font color of the form title toDark Blue, Text 2 (4th column, 1st row in the Theme Colors gallery). (Hint: Ifthis color is not available, check to confirm that the Office theme is applied tothis form. In Step 2, you may have accidentally applied the Integral theme toall objects in the database—instead of just the EmployeeList form.) 6. In the Form header, insert the picture file support_NP_A13_T4_P1a_pst.jpg,available for download from the SAM website. Remove the picture from thecontrol layout, and then move the picture to the right of the form title. Savethe form. (Hint: To insert this image, you will need to use the Logo option inthe Header/Footer section of the DESIGN tab.) 7. In Form View, use the Find command and the BillingData form to find therecord with the Billing ID A80565. Change the number of sessions to 8 and theamount to $800. 8. Add a new record to the Billing table using the BillingData form, enter the datashown in Figure 1 below, and then close the form. Figure 1: Billing Data Table Microsoftproductscreenshotsused withpermissionfromMicrosoftCorporation.Clip artcourtesy ofOffice.com.Copyright© 2014CengageLearning.All RightsReserved. Copyright © 2014 Cengage Learning©. All Rights Reserved.Microsoft product screenshots used with permission from Microsoft Corporation.Reserved.Microsoft 3. New Perspectives Access 2013 | Tutorial 4: SAM Project 1a 9. Use the Form Wizard to create a form containing a main form and a subformas follows:a. Select all fields from the Therapist table for the main form.b. Select the PatientID, StartDate, and EndDate fields from theBilling table.c. Choose the option to view the data by therapist.d. Select the Datasheet layout for the subform.e. Specify the title TherapistsAndBilling for the main form andBillingSubform for the subform. 10. Switch to Layout View and change the title in the main form to Therapistsand Billing, and then change the font color of the title to Dark Blue, Text 2(4th column, 1st row in the Theme Colors gallery). (Hint: If this color is notavailable, check to confirm that the Office theme is applied to this form. InStep 2, you may have accidentally applied the Integral theme to all objects inthe database—instead of just the EmployeeList form.) 11. Resize the Patient ID, Start Date, and End Date columns in the subform tobest fit the data they contain. Resize the width of the subform as shown inFigure 2 below. Copyright© 2014CengageLearning.All RightsReserved. 12. In Form View, use the TherapistsAndBilling form and the Find command to findthe record with Therapist ID 542, and then change the Certification field valueto DPT. Save and close the form. Copyright © 2014 Cengage Learning©. All Rights Reserved.Microsoft product screenshots used with permission from Microsoft Corporation.Reserved.Microsoft Figure 2: Therapists and Billing Form New Perspectives Access 2013 | Tutorial 4: SAM Project 1a13. Use the Report Wizard to create a report based on the primary Patient tableand the related Billing table, as follows:a. Select the PatientID, PatientFirst, PatientLast, and BirthDatefields from the Patient table.b. Select all fields from the Billing table except for the PatientID field.c. View the data by patient and do not select any additional groupinglevels for the report.d. Sort the details records in ascending order by BillingID.e. Select the Outline layout and the Landscape orientation for thereport.f. Specify the report title PatientBilling. 14. Switch to Layout View and apply the Integral theme to the PatientBillingreport only. Change the report title for the PatientBilling report to PatientBilling (two words). 15. Resize and reposition the following objects in the PatientBilling report inLayout View, and then scroll through the report to make sure all field labelsand field values are fully displayed:a. Resize the Patient ID, Patient First, Patient Last, and BirthDate field label boxes on their right sides, decreasing their widthsso that the field label boxes are as wide as the values they contain.b. Move the Patient ID, Patient First, Patient Last, and BirthDate field value boxes to the left, so they are closer to their fieldlabels, to decrease the amount of white space between the fieldlabel and field value boxes for each field.c. Resize the Birth Date field value box on its right side, decreasingits width so that the field value box is as wide as the values itcontains.d. Resize the Amount field label box and the Amount field value boxon their right sides to decrease their widths to approximately 1′,and then move the Amount field label and value box closer to theSessions field (as shown in Figure 3 on the following page).e. Scroll to the bottom of the report, and then resize the control thatcontains the page number on its right side to decrease its width sothat it is only as wide as the “Page 1 of 1” value it contains. Use anarrow key to move the page number control to the left so that itsright edge aligns with the right edge of the Amount field in thereport.f. Save the report. New Perspectives Access 2013 | Tutorial 4: SAM Project 1a 16. Use conditional formatting in the PatientBilling report to format contractamounts that are equal to or greater than $1,000 in a bold, red font, asshown in Figure 3 below. Display the report in Print Preview, review its pages,and then save and close the report. Copyright © 2014 Cengage Learning. All Rights Reserved. Figure 3: Patient Billing Report Save and close any open objects in your database. Compact and repair your database, closeit, and exit Access. Follow the directions on the SAM website to submit your completedproject.