DESCRIPTION
Physical Therapy Specialists, P.C. recently merged with another physical therapy office in Reading, Ohio. Jennifer Christie is integrating the Reading office records into the Physical Therapy Specialists database and plans to enhance the database to meet the center's expanding needs. She needs to manage the records, create tables from existing data, and create queries using related data frommore than one table. She wants to view and create indexes as well. You will use various table relationships and queries to efficiently identify groups of records to delete, append to another table (such as an inactive accounts table), or update a data field in a group of records such as a comment field.
PROJECT STEPS
1. Open the tblBilling table in Design view. View the existing indexes for the table. Change the Sort order for the TherapistID index to Descending order. Close the Indexes dialog box, then save and close the tblBilling table.
2. Open the tblTherapist table in Design view. Create a new index for the tblTherapist table based on the Lastfield. The index should Allow for duplicate values. Save and close the tblTherapist table.
3. Create and run a Make-table queryas described below:
a. Create a Select querybased on the tblPatient table.
b. In the query, include all fields from the tblPatient table.
c. Add a criterion to the query, so that only records with a CurrentPatient field value of False are selected by the query.
d. Save the query using the name qryPatientArchiveMakeTable.
e. Change the query type to a Make-table query, using tblPatientArchiveas the name for the new table and creating the table in the current database.
f. Run the Make-tablequery to create thetblPatientArchivetable(Hint: You will be adding five rows to the new table).
g. Save and close the query.
4. Create an Append queryas described below:
a. Create a Select query based on the tblReadingEmployee table.
b. In the query, add the EmployeeID, LocationID, First, Last, Title, Specialty, Certification fields (from the tbleReadingEmployee table) in that order.
c. Add a criterion to the query, so that only records with a Titlefield value of Therapist are selected by the query.
d. Save the query using the name qryTherapistAppend.
e. Change the query type to an Append query, appending the records to the tblTherapisttable in the current database. You will need to manually set the EmpoyeeID field (from the tlbReadingEmployee table) to append to the TherapistID field (in the tblTherapist table) Hint: The Titlefield values will not be appended to the tblTherapist table.
f. Run the query(Hint: You will append three rows to the table).
g. Save the query and then close it.
5. Create a Delete queryas described below:
a. Create a Select query based on the tblReadingEmployee table.
b. Include all fields from thetblReadingEmployee table in the query.
c. Add a criterion to the query, so that only records with the Title field value of Staffare selected by the query.
d. Save the query with the name qryReadingEmployeeDelete.
e. Change the query type to a Delete query.
f. Run the Delete query (Hint: You will delete threerows from the tblReadingEmployee table).
g. Save the query and then close it.
6. Create and run an Update queryas described below:
a. Create a Select query based on the tblEmployee table.
b. Include all fields from the tblEmployeetable in the query.
c. Add a criterion to the query, so that only records with a Titlefield value of Staff are selected by the query.
d. Save the query with the name qryEmployeeUpdate
e. Change the Select query to an Updatequery.
f. Update the Titlefield value for all records selected by this query fromStaffto Support Staff.
g. Run the update query(Hint: You will update12 rows when you run this query).
h. Save the query and then close it.
7. In the Relationships window, create a Many-to-many relationship between the tblTherapist and the tblPatient tables as follows:
a. Add the tblVisitstable to the Relationships window.
b. Create a One-to-many relationship between the tblPatient table and the tblVisits tableusing the commonPatientID field.
c. Enforce referential integrity, and cascade update related fields.
d. Create a One-to-manyrelationship between the tblTherapist table and the tblVisits table using the common TherapistID field.
e. Enforce referential integrity, and cascade update related fields.
f. Leave the Relationships window open.
8. Create aOne-to-one relationship between the tblPatienttable and the tblEmergencyContacts table.
a. Use the common PatientID field as the join field.
b. Enforce referential integrity, and cascade update related fields.
c. Close the Relationships window, and save your changes if prompted.
9. Create anew query in Query Design view that demonstrates theOne-to-one relationship betweentblPatient and tblEmergencyContacts tables as described below:
a. In Query Design view, add the tblPatienttable and tblEmergencyContacts table to the query window.
b. Add the following fields from the tblPatient table to the new query in the following order: PatientID, PatientFirst, PatientLast.
c. Add the following fields from the tblEmergencyContacts table to the query in the following order:ContactFirst, ContactLast.
d. Save the query using the name qryEmergencyContacts.
e. Run the query.
f. Close the query, save if prompted.
10. Create a new query in Query Design view that demonstrates the Inner join between the tblSpecialties and tblTherapist tables as described below:
a. In Query Design view, add the tblSpecialties and tblTherapist table to the query window.
b. In Query Design view, add Totals to the query.
c. Add the Specialtyfield from the tblSpecialities table to the query. Use the default Group By entry in the Total row.
d. Sort the query in Ascending order by the Specialty field values.
e. After the Specialty field, add the TherapistID field from the tblTherapist table to the query. Update the Total Row for the TherapistID field to use the COUNT aggregate function to count the number of TherapistIDs associated with each Specialty field value.
f. Save the query using the name qrySpecialtyInnerJoin.
g. Run the query and confirm it matches Figure 1.
h. Close the query, saving if necessary.
Attachment:- NP-Access2013-T9-P1a-DarioLozada-Ramirez.rar