Homework answers / question archive / Illustrated Access 2013 Unit B: SAM Project 1a Tours Database USING QUERIES PROJECT DESCRIPTION You work for an adventure tour company that operates recreational hiking, biking, and walking tours in Colorado

Illustrated Access 2013 Unit B: SAM Project 1a Tours Database USING QUERIES PROJECT DESCRIPTION You work for an adventure tour company that operates recreational hiking, biking, and walking tours in Colorado

MS Access

Share With

---

Illustrated Access 2013 Unit B: SAM Project 1a

Tours Database

USING QUERIES

PROJECT DESCRIPTION

You work for an adventure tour company that operates recreational hiking, biking, and

walking tours in Colorado. The company uses a database to track information about tour

guides, tours, tour schedules, clients, and sales. You’ll create and modify several queries to

answer specific questions about the data.

GETTING STARTED

?

Download the following file from the SAM website:

o

IL_Access2013_UB_P1a_

FirstLastName

_1.accdb

?

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

o

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

Use the Simple Query Wizard to create a query based on the

Tours

table with

the following options:

a.

Include all fields from the

Tours

table in the query.

b.

Select the option to

show details

in the query.

c.

Set the title of the query to

TourMasterList

.

Run the query, then save and close it.

2.

In Datasheet View, change the

SalesListing

query font to

Times New Roman

.

3.

In Datasheet View, change the

SalesListing

query font size to

12 pt.

Save and

close the

SalesListing

query.

4.

Create a new query in Query Design View based on the

Tours

and

Schedule

tables with the following options. (

Hint

: Resize the

Tours

field list so all fields

are visible.)

a.

Add the following fields from the

Tours

table to the query grid in

this order:

TourName

,

Level

,

TrailheadCity

, and

Cost

.

b.

Add the following field from the

Schedule

table to the query grid,

after the Cost field:

StartDate

.

c.

Save the query with the title

TourSchedule

, then display it in

Datasheet View.

5.

Use the Save Object As feature to save the

TourSchedule

query as a new

query with the title

TourBeginner

.

6.

Open the

TourBeginner

query in Design View, then add criteria to select only

those records where the

Level

field value equals

Beginner

. Switch to

Datasheet View, confirm the proper records appear in the

TourBeginner

query,

then save and close it.

7.

Create a new query in Query Design View based on the

Clients

table with the

following options. (

Hint

: Resize the

Clients

field list to see all of the fields.)

a.

Add the following fields from the

Clients

table to the query grid in

this order:

LastName

,

FirstName

,

City

, and

State

.

b.

Add an

ascending

sort order to the

LastName

and

FirstName

fields.

c.

Save the query with the name

ClientList

, then display it in

Datasheet View.

8.

Use the Save Object As feature to save the

ClientList

query as a new query

with the title

ClientListTXCO

.

9.

Switch to Design View for the

ClientListTXCO

query, then enter criteria to

select only those records with a

State

field value of

TX

(Texas) or

CO

(Colorado). Switch to Datasheet View, confirm the proper records appear in

the

ClientListTXCO

query, then save and close it.

10.

Create a new query in Query Design View based on the

Tours

table with the

following options. (

Hint

: Resize the

Tours

field list to see all of the fields.)

a.

Add the following fields from the

Tours

table to the query grid in

this order:

TourName

,

MinimumAge

,

TrailheadCity

, and

Cost

.

b.

Add criteria to select only those tours where the

MinimumAge

field

value is greater than or equal to

10

and the

Cost

field value is less

than or equal to

100

. (

Hint

: Remember that all records appearing in

the query will fulfill both criteria—not just one or the other.)

c.

Save the query with the name

LowCostTours

, display the query in

Datasheet View, then close it.

11.

Open the

Clients

table in Datasheet View, then Filter By Selection to find only

those records where the

State

field equals

CA

(California). Make the following

changes to the record for

Greg Huber

(ClientID 6) (

Hint

: Do not include the

periods shown at the end of sub-steps a, b, and c.):

a.

Change the

Address

field to

400 Oak Street

.

b.

Change the

City

field to

Austin

.

c.

Change the

State

field to

TX

.

Remove the filter, then save and close the table.

12.

Open the

SalesListing

query in Design View, then enter criteria that use a

wildcard character to find all records that have a

StartDate

field value of any

day in the month of

June 2015

.

13.

In the

SalesListing

query, delete the

SaleDate

field from the query grid, then

delete the

Guides

table from the query.

14.

In the

SalesListing

query, switch to Datasheet View and hide the

Cost

field.

Save and close the

SalesListing

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.