New PerspectivesAccess 2013
Tutorial 3: SAM Project 1a
PHYSICAL THERAPY SPECIALISTS, P.C.
USING QUERIES TO UPDATE AND RETRIEVE INFORMATIONNamePROJECT DESCRIPTION
Jennifer Christie needs to update some of the data in the Therapist and Location tables to reflect a contract change and the closure of a business location for Physical Therapy Specialists. She also needs to view specific data about patients, therapists, and billing. She asks you to help her maintain the data in the database and to create query objects to view the data she needs to review.
GETTING STARTED
· Download the following file from the SAM website:
o NP_Access2013_T3_P1a_FirstLastName_1.accdb
· Open the file you just downloaded and save it with the name:
o NP_Access2013_T3_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.
· Open the _GradingInfoTabletable 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. Open the Therapist table in Datasheet view. Display the subdatasheet for the record with TherapistID 699, and then update the record with BillingID A84975 to include 3 sessions and an amount of $225. Close the Therapist table.
2. Open the Location table in Datasheet view. Change the font size for the datasheet to 12 pt.
3. Select and resize the Address column in the Location table datasheet to best fit the data it contains.
4. Delete the record with LocationID C from the Location table. Save and close the Location table.
5. Create a new query in Design view and based on the Patient table. Add the FirstName, LastName, BirthDate, and Gender fields, in that order, to the query design. Save the query as PatientBirthdays, and then run it.
6. In the PatientBirthdays datasheet, use Filter By Selection to select only those records for patients with an October birthday. (Tip: Select 10 in one of the records in the PatientBirthdays field and then select the Begins with 10 option in the Selection menu.) Redisplay all records in the datasheet, but do not clear the filter you just applied. Save and close the PatientBirthdays query.
7. Create a new query in Design view that is based on the Patient, Billing, and Therapist tables. Save the query as PatientsAndTherapists, and then do the following:
a. Add the LastName field from the Patient table to the query.
b. Add the LastName field from the Therapist table to the query.
c. Add the StartDate, EndDate, Sessions, and Amount fields, in that order, from the Billing table to the query.
d. Save and run the query, and then close it.
8. Use the Navigation Pane to copy the PatientsAndTherapists query, and then paste it as PatientsAndTherapistsMarchStart. Modify the PatientsAndTherapistsMarchStart query by adding a condition to the StartDate field to select records with contracts that begin on or after March 1,2016. Set the StartDate field so it does not appear in the query results, but remains in the query design. Save and run the query, and then close the query.
9. In the Navigation Pane, copy the PatientsAndTherapistsMarchStart query, rename the copied query as PatientsAndTherapistsMarch, and then do the following:
a. Add a new condition to the query to select records with contracts that start on or after March 1, 2016 and end on or before March 31, 2016.
b. Sort the records in ascending order by the StartDate field.
c. Change the StartDate field so it appears in the query results, and then move the StartDate field so it follows the LastName field in the query design.
d. Save and run the query, and then close it.
10. Create a new query in Design view that uses the Therapist table. Add the LastName, Specialty, Certification, HireDate, and Minors fields, in that order, to the query design. Add a condition to the Certification field to select records that contain the value MPT. Save the query as MPT, run it, and then close it.
11. In the Navigation Pane, copy the MPT query, rename the copied query as MPTOrMinors, and then add a new condition to the MPTOrMinors query to select a record with a Certification field that contains the value MPT or a record that indicates that the therapist accepts patients who are minors. Save and run the query, and then close it.
12. Create a new query in Design view that is based on the Patient and Billing tables. Save the query as PatientAmounts, and then do the following:
a. Add the LastName and FirstName fields from the Patient table to the query.
b. Add the Sessions and Amount fields from the Billing table to the query.
c. Save and run the query.
13. Add the Total row to the PatientAmounts datasheet, and then use a function to calculate the total number of sessions and the total of all contract amounts. Save and close the query.
14. Create a new query in Design view that is based on the Patient and Billing tables. Save the query as ContractDays, and then do the following:
a. Add the LastName field from the Patient table to the query.
b. Add the Sessions, StartDate, and EndDate fields from the Billing table, in that order, to the query.
c. In Design view, create a calculated field named NumberOfDays in the fifth column of the query design grid that determines the number of days in each contract, by creating an expression that subtracts the StartDate field value from the EndDate field value. Set the Caption property for the calculated field to Number of Days. (Hint: Refer to pages AC 157-159 in the textbook for help.)
d. Sort the values in the NumberOfDays field in descending order.
e. Save and run the query. Resize the Number of Days column to best fit the data it contains.
f. Save and close the query.
15. Create a new query in Design view that is based on the Billing and Therapist tables. Save the query as TherapistTotals, and then do the following:
a. Add the TherapistID and LastName fields from the Therapist table to the query.
b. Add the Sessions and Amount fields from the Billing table to the query.
c. For each therapist, use an aggregate function to calculate the total number of sessions and the total amount billed for those sessions. For these two columns, use the field names NumberOfSessions and TotalAmount, and the captions Number of Sessions and Total Amount, respectively.
d. Save and run the query. Resize the Number of Sessions and Total Amount columns to best fit the data they contain.
e. Save and close the query.
Compact and repair your database, save your database, and exit Access. Follow the directions on the SAM website to submit your completed project.
