New Perspectives Access 2013

Tutorial 5: SAM Project 1a

Physical Therapy Specialists, P.C.

creating specialized queries and enhancing a databaseName

PROJECT DESCRIPTIONJennifer Christie wants to enhance the Physical Therapy Specialists database so she can generate specific data about the clinics therapists, patients, billing, employees, and locations. She also wants to make changes to the tblLocation and tblPatient tables to streamline data entry. Youll create several queries to answer Jennifers questions about where patients are located geographically, which employees have entry-level salaries, which patients live in the same household, which therapists have no billing records being processed, and the total payroll for each position at each clinic location.

GETTING STARTED

Download the following file from the SAM website:

o NP_Access2013_T5_P1a_FirstLastName_1.accdb

Open the file you just downloaded and save it with the name:

o NP_Access2013_T5_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 _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 query to find all records in the tblPatient table in which the Phone field value starts with the area code 234. Include the following options:

Display all fields from the tblPatient table in the query in the following order: PatientID, PatientFirst, PatientLast, Address, City, State, Zip, Phone, BirthDate, Gender.

Sort the query in ascending order by the City field.

Save the query as qry234AreaCode, run the query, and then close it.

2. Make a copy of the qry234AreaCode query, name it qryNotDueberRichvilleWaco, and then make the following updates:

Delete the condition from the Phone field.

Add a new condition to find all records in the tblPatient table in which the City field values are not Dueber, Richville, or Waco. Use a list-of-values match for the selection criteria.

If necessary, sort the query in ascending order by the City field. (Hint: Depending on how Step 1 was completed, this query may already be sorted in ascending order by the City field.)

Save and run the query, and then resize the Address column to best fit.

Save and close the query.

3. Create a query to find all records from the tblEmployee table in which the MonthlySalary value is 1500, 1850, or 1875. Use a list-of-values match for the selection criteria. The query should have the following options:

Display all fields from the tblEmployee table in the query in the following order: EmployeeID, LocationID, First, Last, Title, Monthly Salary.

Sort in descending order by the MonthlySalary field.

Save the query as qryEntryLevelSalaries, run the query, and then close it.

4. Create a query to display all records from the tblTherapist table with the following options:

Display only the LocationID, Specialty, and Certification fields (in that order) from the tblTherapist table in the query.

Sort the query in ascending order by the LocationID field.

Add a calculated field named TherapistName as the first column in the query that concatenates the First field value, a space, and the Last field value.

Set the Caption property for the TherapistName field to Therapist Name.

Save the query as qryTherapistSpecialties, run the query, and then resize the Therapist Name column to best fit.

Save and close the query.

5. Create a parameter query to select the tblTherapist table records for a Certification field value that the user specifies. The query should have the following options:

Display the LocationID, First, Last, Specialty, and Certification fields (in that order) in the query.

Use Enter the certification: as the prompt associated with the Certification field prompt. If the user doesnt enter a Certification field value, the parameter query should select all records from the tblTherapist table.

Sort the query in ascending order by the Last field.

Save the query as qryCertificationParameter.

Confirm the parameter query is working correctly by running the query and entering no value as the Certification field value. Then run the query again and enter DPT as the Certification field value. Close the query.

6. Create a find duplicates query based on the tblPatient table with the following options:

Select the Address field as the field that might contain duplicates.

Select the PatientID, PatientFirst, PatientLast, and Phone fields (in that order) as additional fields in the query recordset.

Save the query as qryDuplicateHouseholds, run the query, and then close it.

7. Create a find unmatched query that finds all records in the tblTherapist table for which there is no matching record in the tblBilling table. Display the TherapistID, LocationID, First, Last, and HireDate fields from the tblTherapist table in the query recordset. Save the query as qryTherapistsWithoutMatchingBilling, run the query, and then close it.

8. Create a query to display all fields from the tblEmployee table in the following order: EmployeeID, LocationID, First, Last, Title, and Monthly Salary, sorted in descending order by MonthlySalary. Use the Top Values property to select the top five records. Save the query as qryTop5Salaries, run the query, and then close it.

9. Open the tblLocation table in Design View. Change the ManagerID field to a lookup field with the following options:

Specify that the lookup field values will come from another table or query.

Select the Last field, and then select the EmployeeID field from the tblEmployee table.

Sort the values in ascending order by the Last field.

Confirm the Hide the key column (recommended) option is checked.

Resize the lookup column to best fit.

Accept the default label for the lookup column.

Save the changes, and then view the tblLocation table in Datasheet View.

Change the Manager ID field value for the record with Location ID A to Eleby.

Close the table.

10. Open the tblBilling table in Design View. Create a field validation rule for the Sessions field to only allow values >0. Enter Patients must participate in at least 1 session. (including the period) as the validation text. Save and close the tables, clicking Yes when warned about the data integrity rule.

11. Open the tblPatient table in Design View. Use the Input Mask Wizard to add an input mask to the Phone field. The input mask should use parentheses as separators for the area code, a space between the area code and the number, and a dash between the second and third groups of numbers, as in (123) 456-7890, with only the last seven digits required. Do not store the literal display characters if you are prompted to do so. Save the tblPatient table and switch to Datasheet View. Change the Phone field value for the record with PatientID A10026 to (234) 555-0001.

12. View the tblPatient table in Design View and define a field validation rule for the Gender field in the tblPatient table. Acceptable field values for the Gender field are F and M (in that order). Use the message Must be F or M to notify a user who enters an invalid Gender field value. Save and close the table, clicking Yes when warned about data integrity rules.

Switch to Datasheet View and test the field validation rule for the Gender field, making sure any tested field values are the same as they were before your test. (Hint: You can confirm this by retyping the correct value or by pressing the Esc key). Close the table.

13. Create a crosstab query based on the tblEmployee table with the following options:

Use the LocationID field values for the row headings.

Use the Title field values for the column headings.

Use the sum of the MonthlySalary field values as the summarized value.

Include row sums.

Save the query as qryMonthlySalaryByTitle.

Run the query, and then resize the columns in the query recordset to best fit.

Save and close the query.

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.