New Perspectives Access 2013 Tutorial 3: SAM Project 1b

PRN NURSING SERVICES

USING QUERIES TO UPDATE AND RETRIEVE INFORMATION NAME

PROJECT DESCRIPTION

Amy Rosario needs to update some of the data in the Contractor and Office tables to reflect

a contract change and the closure of a business location for PRN Nursing Services. She also

needs to view specific data about clients, contractors, 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_P1b

_FirstLastName_

1.accdb

?

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

o

NP_Access2013_T3_P1b_

FirstLastName

_2.accdb

o

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

Contractor

table in Datasheet view. Display the subdatasheet for the

record with ContractorID 1015, and then update the record with BillingID 2009

to include

30

total hours and an hourly rate of

$60

. Close the

Contractor

table.

2.

Open the

Office

table in Datasheet view. Change the font size for the

datasheet to

12 pt

.

3.

Select and resize the Office ID, State, and Zip columns in the

Office

table

datasheet to best fit the data they contain.

4.

Delete the record with OfficeID 103 from the

Office

table. Save and close the

Office

table.

5.

Create a new query in Design view that is based on the

Client

table. Add the

FirstName

,

LastName

,

ClientSince

, and

Corp

fields, in that order, to the

query design. Save the query as

ClientStartDates

, and then run it.

6.

In the

ClientStartDates

query datasheet, use Filter By Selection to select only

those records for clients that were contracted in the year

2014

. (

Tip:

Select

2014 in the

Client Since

field

and then select the

Ends with 2014

option in

the Selection menu.) Redisplay all records in the datasheet, but do not clear

the filter you just applied. Save and close the

ClientStartDates

query.

7.

Create a new query in Design view that is based on the

Client

,

Billing

, and

Contractor

tables. Save the query as

ClientsAndContractors

, and then do

the following:

a.

Add the

LastName

field from the

Client

table to the query.

b.

Add the

LastName

field from the

Contractor

table to the query.

c.

Add the

StartDate

,

EndDate

,

TotalHours

, and

HourlyRate

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

ClientsAndContractors

query, and then

paste it as

ClientsAndContractorsJulyStart

. Modify the

ClientsAndContractorsJulyStart

query by adding a condition to the StartDate

field to select records with clients that were contracted on or after

July 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

ClientsAndContractorsJulyStart

query,

rename the copied query as

ClientsAndContractorsJuly

, and then do the

following:

a.

Add a new condition to the query to select records with contracts

that start on or after

July 1, 2016

and end on or before

July 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 based on the

Contractor

table. Add the

LastName

,

Interests

,

Credentials

,

HireDate

, and

PA

fields, in that order,

to the query design. Add a condition to the Credentials field to select records

that contain the value

MSN

. Save the query as

MSN

, run it, and then close it.

11.

In the Navigation Pane, copy the

MSN

query, rename the copied query as

MSNOrPA

, and then add a new condition to the

MSNOrPA

query to select a

record with a Credentials field that contains the value

MSN

or a record that

indicates that the contractor is a

physician assistant

. Save and run the

query, and then close it.

12.

Create a new query in Design view that is based on the

Client

and

Billing

tables. Save the query as

ClientAmounts

, and then do the following:

a.

Add the

LastName

and

FirstName

fields from the

Client

table to

the query.

b.

Add the

TotalHours

and

HourlyRate

fields from the

Billing

table

to the query. (

Tip:

The fields should be in the order

LastName,

FirstName

,

TotalHours,

and

HourlyRate

.)

c.

Save and run the query.

13.

Add the Total row to the

ClientAmounts

query datasheet, and then use a

function to calculate the total number of hours and the average hourly rate.

Save and close the query.

14.

Create a new query in Design view that is based on the

Client

and

Billing

tables. Save the query as

ContractDays

, and then do the following:

a.

Add the

LastName

field from the

Client

table to the query.

b.

Add the

TotalHours

,

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

.

d.

Sort the records by 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

Contractor

tables. Save the query as

ContractorTotals

, and then do the following:

a.

Add the

OfficeID

field from the

Contractor

table to the query.

b.

Add the

TotalHours

and

HourlyRate

fields from the

Billing

table

to the query.

c.

For each office, use an aggregate function to calculate the total

number of hours and the average hourly rate for contractors

assigned to each location. For these two columns, use the field

names

TotalContractHours

and

AverageHourlyRate

, and the

captions

Total Contract Hours

and

Average Hourly Rate

,

respectively.

d.

Save and run the query. Resize the Total Contract Hours and

Average Hourly Rate 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