New Perspectives Access 2013 Tutorial 3: SAM Project 1a

Physical Therapy

Specialists, P.C.

USING QUERIES TO UPDATE AND RETRIEVE INFORMATION NAME

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 a

nd 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.

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.

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 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.

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.

pur-new-sol

Related Questions