Illustrated Access 2013 Unit B: SAM Project 1b Training Database USING QUERIES PROJECT DESCRIPTION You work for a software development company that builds mobile apps

Illustrated Access 2013 Unit B: SAM Project 1b

Training Database

USING QUERIES

PROJECT DESCRIPTION

You work for a software development company that builds mobile apps. The company uses a

database to track information about internal training for employees. 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_P1b_

FirstLastName

_1.accdb

?

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

o

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

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

Classes

table

with the following options:

a.

Include all fields from the

Classes

table in the query.

b.

Select the option to show details in the query.

c.

Set the title of the query to

ClassMasterList

.

Run the query, then save and close it.

2.

In Datasheet View, change the

EnrollmentListing

query font to

Arial Narrow

.

3.

In Datasheet View, change the

EnrollmentListing

query font size to

10 pt.

Save and close the

EnrollmentListing

query.

4.

Create a new query in Query Design View based on the

Classes

and

Schedule

tables with the following options. (

Hint

: Resize both field lists so all fields are

visible.)

a.

Add the following fields from the

Classes

table to the query grid in

this order:

ClassName

,

ContactHours

,

SeatsOpen

, and

Fee

.

b.

Add the following fields from the

Schedule

table to the query grid,

after the

Fee

field:

StartDate

,

Building

, and

RoomNumber

.

c.

Save the query with the title

ClassSchedule

and display it in

Datasheet View.

5.

Use the Save Object As feature to save the

ClassSchedule

query as a new

query with the title

ClassSchedule2Hours

.

6.

Open the

ClassSchedule2Hours

query in Design View, then add criteria to

select only those records where the

ContactHours

field value equals

2

. Switch

to Datasheet View, confirm the proper records are appearing in the

ClassSchedule2Hours

query, then save and close it.

7.

Create a new query in Design View based on the

Employees

table with the

following options:

a.

Add the following fields from the

Employees

table to the query grid

in this order:

LastName

,

FirstName

,

Extension

, and

Department

.

b.

Add an

ascending

sort order to the

LastName

and

FirstName

fields.

c.

Save the query with the name

EmployeeList

and display it in

Datasheet View.

8.

Use the Save Object As feature to save the

EmployeeList

query as a new

query with the title

MarketingPersonnel

.

9.

Switch to Design View for the

MarketingPersonnel

query, then enter criteria to

select only those records with a

Department

field value of

Marketing

or

Personnel

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

the

MarketingPersonnel

query, then save and close it.

10.

Create a new query in Design View based on the

Classes

table with the

following options. (

Hint

: Resize the

Classes

field list to see all of the fields.)

a.

Add the following fields from the

Classes

table to the query grid in

this order:

ClassName

,

ContactHours

,

SeatsOpen

, and

Fee

.

b.

Add criteria to select only those classes where the

ContactHours

field value is

greater than 2

and the

Fee

field value is

equal to

or less than 250

. (

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

AffordableClasses

, display the

query in Datasheet View, then close it.

11.

Open the

Employees

table in Datasheet View, then Filter By Selection to find

only those records where the

Department

field equals

Information Systems

.

Make the following changes to the record for

Christi Nielsen

(EmployeeID

19):

a.

Update the

FirstName

field to

Christine

.

b.

Update the

LastName

field to

Samsung

.

c.

Update the

Extension

field to

543

.

Remove the filter, then save and close the table.

 

12.

Open the

EnrollmentListing

query in Design View. Enter criteria that use a

wildcard character to find all records that have a

StartDate

field value of any

day in the month of

July 2015

.

13.

In the

EnrollmentListing

query, delete the

EnrollmentDate

field from the query

grid, then delete the

Trainers

table from the query.

14.

In the

EnrollmentListing

query, switch to Datasheet View and hide the

Fee

field. Save and close the

EnrollmentListing

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.