New Perspectives Access 2013 Tutorial 5: SAM Project 1b Easy Green Lawn Care CREATING SPECIALIZED QUERIES AND ENHANCING A DATABASE PROJECT DESCRIPTION Easy Green Lawn Care provides services such as lawn mowing, mulching, and seeding at fixed hourly rates

Subject:MS AccessPrice: Bought3

Share With

New Perspectives Access 2013 Tutorial 5: SAM Project 1b

Easy Green Lawn Care

CREATING SPECIALIZED QUERIES AND ENHANCING A DATABASE

PROJECT DESCRIPTION

Easy Green Lawn Care provides services such as lawn mowing, mulching, and seeding at

fixed hourly rates. Abby Barker, office manager for Easy Green Lawn Care, uses an Access

database to generate specific data about the company’s employees, customers, services,

and revenue. You will help Abby create queries to answer specific questions about the

business. You will also help her modify tables to improve data accuracy.

GETTING STARTED

?

Download the following file from the SAM website:

o

NP_Access2013_T5_P1b

_FirstLastName_

1.accdb

?

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

o

NP_Access2013_T5_P1b_

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.

Create a query to find all records in the

tblCustomer

table in which the

Address

field value contains the word

Market

anywhere in the field value.

Include the following options:

a.

Display all fields from the

tblCustomer

table in the query in the

following order:

CustomerID

,

CustomerFirst

,

CustomerLast

,

Address

,

City

,

State

,

Zip

,

Phone

.

b.

Sort the query in

ascending

order by the

CustomerLast

field

in Design View.

Save the query as

qryMarket

, run the query, and then close it.

2.

Make a copy of the

qryMarket

query, rename it

qryNotBFH

,

and then make

the following updates:

a.

Delete the criterion from the

Address

field.

b.

Add new criteria to find all records in the

tblCustomer

table in

which the

City

field values

are not

Brentwood

,

Franklin

, or

Hendersonville

. Use a list-of-values match for the selection

criteria.

c.

If the query is not already sorted, sort the query in

ascending

order by the

CustomerLast

field in Design View.

d.

Save and run the query, and then resize the

Address

column to

best fit

.

Save and close the

qryNotBFH

query.

3.

Create a query to find all records from the

tblStaff

table in which the

MonthlySalary

value is

2000

,

2250

, or

2400

. Use a list-of-values match for

the selection criteria. The query should have the following options:

a.

Display these fields from the

tblStaff

table in the query in the

following order:

StaffID

,

OfficeID

,

StaffFirst

,

StaffLast

,

StaffTitle

,

MonthlySalary

.

b.

In Design View, sort the query in

descending

order by the

MonthlySalary

field.

Save the query as

qryEntry2000s

, run the query, and then close it.

4.

Create a query to display all records from the

tblStaff

table with the following

options:

a.

Display the

StaffID

,

OfficeID

, and

StaffTitle

fields (in that

order) in the query.

b.

Sort the query in

ascending

order by the

StaffID

field in

Design View.

c.

Add a calculated field named

EmpName

as the first column in

the query that concatenates the

StaffFirst

field value, a

space

,

and the

StaffLast

field value.

d.

Set the Caption property for the

EmpName

field to

Employee

Name

.

e.

Save the query as

qryEmployeeList

, run the query, and then

resize the

Employee Name

column to

best fit

.

Save and close the query.

5.

Create a

parameter

query to select the

tblCustomer

table records for a

Zip

field value that the user specifies. Include the following options:

a.

Display the

CustomerFirst

,

CustomerLast

,

Address

,

City

,

State

, and

Zip

fields (in that order) in the query.

b.

Use

Enter the 5-digit zip code:

as the

Zip

field prompt. If the

user doesn’t enter a

Zip

field value, the parameter query should

select all records from the

tblCustomer

table.

c.

Sort the query in

ascending

order by the

CustomerLast

field

in Design View.

d.

Save the query as

qryZipParameter

.

Confirm the parameter query is working correctly by running the query and

entering no value as the

Zip

field value. Then run the query again and enter

37077

as the

Zip

field value. Close the query.

6.

Create a

find duplicates

query based on the

tblServices

table with the

following options:

a.

Select the

ServiceName

field as the field that might contain

duplicates.

b.

Select the

ServiceID

,

OfficeID

, and

HourlyServiceFee

fields

(in that order) as additional fields in the query recordset.

Save the query as

qryDuplicateServices

, run the query, and then close it.

7.

Create a

find unmatched

query that finds all records in the

tblCustomer

table for which there is

no matching record

in the

tblSales

table. The tables

are linked by the common

CustomerID

field. Display the

CustomerID

,

CustomerFirst

,

CustomerLast

, and

Phone

fields (in that order) from the

tblCustomer

table in the query recordset. Save the query as

qryCustomersWithoutMatchingSales

, run the query, and then close it.

8.

Create a query to display all fields from the

tblStaff

table in the following

order:

StaffID

,

OfficeID

,

StaffFirst

,

StaffLast

,

StaffTitle

,

HireDate

, and

MonthlySalary

, sorted in

descending

order by

MonthlySalary

in Design

View. Use the

Top Values

property to select the

Top 10%

. Save the query as

qryTop10Percent

, run the query, and then close it.

9.

Open the

tblStaff

table in Design View. Change the

OfficeID

field to a

lookup

field with the following options:

a.

Specify that the lookup field values will

come from another

table or query

.

b.

Select the

OfficeID

field and the

OfficeName

field from the

tblOffice

table.

c.

Sort the values in

ascending

order by the

OfficeName

field.

d.

Confirm that the

Hide the key column (recommended)

option is checked.

e.

Resize the lookup column to

best fit

.

f.

Accept the default label for the lookup column.

g.

Save the changes and view the

tblStaff

table in Datasheet View.

h.

Change the

OfficeID

field value for the record with StaffID 901

(David Stearns) to

Tradewind Service Center

.

Close the table.

10.

Open the

tblSales

table in Design View. Create a

field validation

rule for the

TotalHours

field to only allow values

greater than or equal to 1

. Enter

1

hour minimum

as the validation text. Save and close the tables. Click

Yes

in

the Data Integrity rule prompt.

11.

Open the

tblCustomer

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 closing

parenthesis and the number, an

underscore (_)

as the placeholder, 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

tblCustomer

table and

switch to Datasheet View. Change the

Phone

field value for the record with

CustomerID B505 (David Truax) to

(615) 555-2233

. Close the

tblCustomer

table.

12.

View the

tblStaff

table in Design View and define a field validation rule for the

HireDate

field. Acceptable field values for the

HireDate

field are

>=1/1/1990

. Use the message

Must be on or after 1/1/1990

to notify a

user who enters an invalid

HireDate

field value. Save the table. Click

Yes

in

the Data Integrity rule prompt.

Switch to Datasheet View and test the field validation rule for the

HireDate

field. Make sure any tested field values are the same as they were before your

testing. (

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

qryServices

query with the following

options:

a.

Use the

ServiceName

field values for the row headings.

b.

Use the

City

field values for the column headings.

c.

Use the

sum

of the

TotalHours

field values as the summarized

value.

d.

Include

row sums

.

e.

Save the query as

qryServiceHoursByCity

.

f.

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.