Homework answers / question archive / New Perspectives Access 2016 | Module 1: SAM Project 1a Healthy Myles Fitness Center CREATING A TABLE, QUERY, FORM, AND REPORT GETTING STARTED ? Open the file NP_AC16_1a_ FirstLastName _1

New Perspectives Access 2016 | Module 1: SAM Project 1a Healthy Myles Fitness Center CREATING A TABLE, QUERY, FORM, AND REPORT GETTING STARTED ? Open the file NP_AC16_1a_ FirstLastName _1

MS Access

Share With

---

New Perspectives Access 2016 | Module 1: SAM Project 1a

Healthy Myles Fitness Center

CREATING A TABLE, QUERY, FORM, AND REPORT

GETTING STARTED

?

Open the file

NP_AC16_1a_

FirstLastName

_1.accdb

, available for download

from the SAM website.

?

Save the file as

NP_AC16_1a_

FirstLastName

_2.accdb

by changing the “1” to

a “2”.

o

If you do not see the

.accdb

file extension

in the Save As dialog box, do

not type it. The program will add the file extension for you automatically.

?

To complete this Project, you will also need to download and save the following

support file from the SAM website:

o

Support_NP_AC16_1a_Equipment.accdb

?

Open the

_GradingInfoTable

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

You are employed at Healthy Myles Fitness Center and are in charge of

consolidating the company’s records and other important information into a

database. The owners expect that the database will help them run the business

more smoothly. You start by setting up the basic structure of the database.

Create a new table in Design View:

a.

Create a new field in the table with the name

ClientID

.

b.

Change the data type of the

ClientID

field to

Short Text

, and set it as the

primary key.

c.

Create the following fields in the table, using the Short Text data type, in

the order shown:

FirstName

,

LastName

,

Address

,

City

,

State

, and

Zip

.

d.

Save the table as

tblClient.

2.

Switch the

tblClient

table to Datasheet View, then enter the records shown in

Table 1 on the next page. (If necessary, resize the

Address

field to best fit the

data it contains.) Close the

tblClient

table, saving if necessary.

Table 1: tblClient Table Records

ClientID

FirstName

LastName

Address

City

State

Zip

A10027

Jenny

Lin

220 Delaware

Ave.

Buffalo

NY

14202

B18423

Adam

Smith

266 Elmwood

Ave.

Buffalo

NY

14222

3.

Open the

tblLocation

table in Design View, and then update the table as

described below:

a.

Change the name of the

ID

field to

LocationID

.

b.

Change the data type of the

LocationID

field to

Short Text

.

c.

Save the table, but do not close it.

4.

With the

tblLocation

table still open in Design View, change the data type of the

LocationOpened

field to

Date/Time

. Save, but do not close, the table.

5.

With the

tblLocation

table still open, switch to Datasheet View, saving the table

if necessary, and then enter the records shown in Table 2 below into the

tblLocation

table. Close the

tblLocation

table.

Table 2: tblLocation Table Records

LocationI

D

LocationNam

e

LocationOpene

d

Address

City

State

ZipCode

Phone

301

Elmwood

5/2/2014

1400

Elmwood

Ave.

Buffalo

NY

14216

716-

555-

1212

308

Sheridan

1/5/2015

5200

Sheridan

Dr.

Buffalo

NY

14221

716-

555-

7621

6.

You have been provided with a support file that contains a table with additional

equipment data. The

tblEquipment

table has the same design as the

tblGym

table in the support file. Copy all the records from the

tblGym

table in the file

Support_NP_AC16_1a_Equipment.accdb

, available for download from the

SAM website, and paste them at the end of the

tblEquipment

table in the

current database. Close the

tblEquipment

table.

7.

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

tblTrainer

table as

described below:

a.

Include the

FirstName

,

LastName

, and

Specialty

fields (in that order) from

the

tblTrainer

table in the query.

b.

Name the query

qrySpecialties

, and then close the query.

8.

You now want to create a simple form to allow employees to update information

on the trainers employed at Healthy Myles Fitness Center.

a.

Use the Form tool to create a simple form based on the

tblTrainer

table.

b.

Save the form with the name

frmTrainer

.

9.

Switch the

frmTrainer

form to Form View, use the navigation buttons to navigate

to the third record (with the

TrainerID

field value 454), and then change the

Certification

field value to

ACSM

.

10.

With the

frmTrainer

form still open in Form View, navigate to the last record

(with the

TrainerID

field value 709) in the

frmTrainer

form, and then change the

Specialty

field value to

Strength Training

. Navigate back to the first record in

the

tblTrainer

table, and then close the form.

11.

Use the Report tool to create a report based on the

tblEquipment

table. Save

the report as

rptEquipmentList

, then close the report.

12.

You decide to adjust the formatting of the

rptTrainer

report to make it easier to

read and to make it fit on one page.

a.

Open the

rptTrainer

report in Layout View, and then reduce the width of

the

TrainerID

column so it is approximately as wide as the text “TrainerID”

in the column heading, or

0.8

".

b.

Select the text box that appears below the

TrainerID

column (and contains

the number 9) in the

rptTrainer

report. Then use the mouse to resize the

height of the text box slightly so that it is tall enough to completely

display the value it contains, or approximately

0.27

" high, as shown in

Figure 1 on the next page. Save the report.

c.

Select the text box that appears below the

Specialty

column and that

contains the text “

Page

1 of 1

”. Use an arrow key to move the text box to

the left so that its left edge vertically aligns with the left edge of the

Specialty

column above it. Right-align the text. View the report in Print

Preview to see the changes you made. Save and close the report

Figure 1: rptTrainer Report

Save and close any open objects in your database. Compact and repair your database,

close it, and then exit Access. Follow the directions on the SAM website to submit your

completed project.