CREATING A TABLE, QUERY, FORM, AND REPORT TO MANAGE THERAPIST DATA
PROJECT DESCRIPTION
Jennifer Christie, a licensed physical therapist, opened the Physical Therapy Specialists clinic in 2009. Over time, Jennifer has expanded her business to include a staff of therapists with different specialties to treat patients who are referred to her clinic by a nearby medical center. Jennifer recently expanded her business to include a second location and several new employees, which has necessitated a change in how she manages the clinic’s operations. She has decided to use Access 2013 to organize her business data. You’ll help Jennifer create and maintain an Access database to store data about therapists, patients, and billing.
GETTING STARTED
• Download the following file from the SAM website:
o NP_Access2013_T1_P1a_FirstLastName_1.accdb
• Open the file you just downloaded and save it with the name:
o NP_Access2013_T1_P1a_FirstLastName_2.accdb
o Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically.
• To complete this Project, you will also need to download and save the following support file from the SAM website:
o support_NP_A13_T1_P1a_staff.accdb
• 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. Create a new table in Datasheet View. Save the table as Patient.
2. Change the name of the ID field in the Patient table to PatientID.
3. Change the data type of the PatientID field to Short Text.
4. Create the following fields in the Patient table in this order, using the Short Text data type: FirstName, LastName, Address, City, State, and Zip.
5. Enter the records shown in Table 1 below in the Patient table.
Table 1: Patient Table
________________________________________
PatientID FirstName LastName Address City State Zip
A10026 Al Pandola 45 South Main St Akron OH 44307
B19356 Linda Francis 235 Hwy 5 Akron OH 44308
6. Resize the Address field to best fit the data it contains. Save and close the Patient table.
7. Open the Therapist table in Datasheet View, and then change the name of the ID field to TherapistID.
8. Change the data type of the TherapistID field to Short Text.
9. Change the data type of the HireDate field to Date/Time, and then save the table.
10. Enter the records shown in Table 2 below into the Therapist table.
Table 2: Therapist Table
________________________________________
TherapistID FirstName LastName Specialty Certification HireDate
501 Jacob Steinley Post-surgical rehab DPT 5/2/2016
508 Belinda Roberts Running-related DPT 1/5/2015
11. Jennifer created a database named support_NP_A13_T1_P1a_staff.accdb, available for download from the SAM website, that contains a table with additional therapist data. The Therapist table you just edited has the same design as the Employee table in the support_NP_A13_T1_P1a_staff.accdb database. Copy all the records from the Employee table in the support_NP_A13_T1_P1a_staff.accdb database and paste them at the end of the Therapist table in the current database.
12. Resize the Specialty column in the Therapist datasheet to best fit the data it contains. Save the table, and then close it.
13. Use the Simple Query Wizard to create a query based on the Therapist table. Include the FirstName, LastName, and Specialty fields from the Therapist table in the query, in that order. Name the query Specialties, and then close the query.
14. Use the Form tool to create a form based on the Therapist table. Save the form as TherapistInfo.
15. In Form View, use the navigation buttons for the TherapistInfo form to navigate to the third record (for TherapistID 409), and then change the Certification field value to DPT.
16. Navigate to the last record (for TherapistID 702) in the TherapistInfo form, and then change the Specialty field value to Running-related. Navigate back to the first record in the Therapist table, and then close the form.
17. Use the Report tool to create a report based on the Therapist table. Save the report as TherapistList.
18. In Layout View for the TherapistList report, use the mouse to reduce the width of the TherapistID column so it is approximately as wide as the text “TherapistID” in the column heading.
19. Select the text box that appears below the TherapistID column (and contains the number 11) in the TherapistList report. Then use the mouse to resize the height of the text box slightly (by approximately 1/16″) so that it is tall enough to completely display the value it contains, as shown in Figure 1 on the following page. Save the report.
Figure 1: Therapist Report
________________________________________
20. Close the Navigation pane. Select the text box that appears below the Specialty and Certification columns and that contains the text “Page 1 of 1.” Use an arrow key to move the text box to the left so that its left edge vertically aligns with the left edge of the Specialty column above it. View the first page of the report in Print Preview to review the changes you made, and then view the next page of the report in Print Preview. Save and close the report, and then open the Navigation pane.
Save and close any open objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project.