New Perspectives Access 2013
Tutorial 3: SAM Project 1a Physical Therapy
Specialists, P.C.
USING QUERIES TO UPDATE AND RETRIEVE INFORMATIONNAME
PROJECT 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 _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. Open the Therapist table in Datasheet View. Display the subdatasheet for the
record with Therapist ID 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. New Perspectives Access 2013 | Tutorial 3: SAM Project 1a
6. In the PatientBirthdays datasheet, use Filter By Selection to select only those
records for patients with an October birthday. (Hint: 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 and rename it 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,
paste the copied query and rename it 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, paste the copied query and
rename it 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. New Perspectives Access 2013 | Tutorial 3: SAM Project 1a 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. 15. Save and close the query. 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 use 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. New Perspectives Access 2013 | Tutorial 3: SAM Project 1a
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.

 

• 1493366215-Assignment_NP_Access2013_T3_P1a-1.docx

SuperTutor

(15)

Status NEW Posted 28 Apr 2017 07:04 AM My Price 20.00

-----------

Attachments

file 1493366218-Solutions file.docx preview (56 words )

S-----------olu-----------tio-----------ns -----------fil-----------e -----------Hel-----------lo -----------Sir-----------/Ma-----------dam----------- T-----------han-----------k y-----------ou -----------for----------- yo-----------ur -----------int-----------ere-----------st -----------and----------- bu-----------yin-----------g m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l b-----------e q-----------uic-----------kly----------- on-----------lin-----------e a-----------nd -----------giv-----------e y-----------ou -----------exa-----------ct -----------fil-----------e a-----------nd -----------the----------- sa-----------me -----------fil-----------e i-----------s a-----------lso----------- se-----------nt -----------to -----------you-----------r e-----------mai-----------l t-----------hat----------- is----------- re-----------gis-----------ter-----------ed -----------onÂ----------- th-----------is -----------web-----------sit-----------e. ----------- H-----------YPE-----------RLI-----------NK -----------&qu-----------ot;-----------htt-----------p:/-----------/wo-----------rkb-----------ank-----------247-----------.co-----------m/&-----------quo-----------t; -----------\t -----------&qu-----------ot;-----------_bl-----------ank-----------&qu-----------ot;----------- -----------Tha-----------nk -----------you----------- -----------

Not Rated(0)

• Buy Answer