Graded Project

Microsoft Access 2016

© PENN FOSTER, INC. 2016

 INTRODUCTION

CONTENTS

INTRODUCTION 2

INSTRUCTIONS 2

SCORING GUIDELINES 16

SUBMITTING YOUR PROJECT 18

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 1
Graded Project
GRADED PROJECT

MICROSOFT ACCESS 2016

INTRODUCTION
The purpose of this project is to create a home inventory database that can be referred
to in case of damage, theft, or natural disaster. You’ll assume the role of homeowner, who
over time has acquired many valuable assets. You’d like to more easily manage purchase
history, product serial and model numbers, warranties, and appraisal information. For this
project, you must create an Access database with forms, queries, and reports that you
will store off site in case of emergency.

INSTRUCTIONS

CREATE A DATABASE
1. Start Access and create a Blank database, naming it “Inventory.”

2. On the Home tab, click View and then name the new table “Inventory.”

3. In Design view, change the field name ID to Item ID and verify that it is data type
AutoNumber and that it has been selected as the Primary Key.

4. In Design view, add the remaining fields and corresponding data types:

Field Name Data Type Description

Item Name Short Text
Category Short Text Appliances, Electronics,
Jewelry, Other
Manufacturer Short Text
Model Short Text
Serial Number Short Text
Purchase Date Date/Time (Short Date)
Purchase Price Currency
Merchant ID Number
Online Purchase Yes/No
Credit Card Purchase Yes/No
Warranty Type Short Text Store, Manufacturer, Other
Warranty Length Short Text
Repair Yes/No
Repair Date Date/Time (Short Date)
Comments Short Text

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 2
Graded Project
 5. Save and then close the Inventory table.

6. Create a second table in Design view. Name the table “Merchants.”

7. Add the following fields and corresponding data types. Be sure Merchant ID is the
Primary key:

Field Name Data Type Description

Merchant ID AutoNumber
Merchant Name Short Text
Address Short Text
City Short Text
State Short Text
Zip Short Text
Email Hyperlink
Website Hyperlink
Telephone Short Text
Fax Short Text
8. Save and then close the Merchants table.

9. Create a relationship by linking the Merchant ID in the Inventory table to the

Merchant ID in the Merchants table. Be sure to enforce referential integrity.

CREATE FORMS AND POPULATE THE DATABASE

1. Create a Merchants form that looks similar to Figure 1.

Figure 1

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 3
Graded Project
 2. Create an Inventory form that looks similar to Figure 2.

Figure 2

3. Use the Merchants form to populate the appropriate sections in the Merchants
table with the records listed below.

Merchant Records
Merchant ID 1
Merchant Name Electronics Mart
Address 65 Resister Ave.
City Blankston
State PA
Zip 18454
Email emart@blanknet.com
Website www.emart.com
Telephone (570) 555-1111
Fax (570) 555-1112

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 4
Graded Project
 Merchant ID 2
Merchant Name Appliances Inc.
Address 2020 Mechanics Road
City Blankston
State PA
Zip 18454
Email n/a
Website n/a
Telephone (570) 555-1234
Fax (none)

Merchant ID 3
Merchant Name Stuff Mart
Address 721 Frengburg St.
City Shopville
State NY
Zip 10022
Email custserv@stuffmart.com
Website www.stuffmart.com
Telephone (212) 555-5432
Fax n/a

Merchant ID 4
Merchant Name Phones and More
Address 21 Framer Circle
City Klossville
State PA
Zip 19019
Email callus@phonesandmore.com
Website www.phonesandmore.com
Telephone (612) 555-9876
Fax n/a

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 5
Graded Project
 Merchant ID 5
Merchant Name Jewelry Warehouse
Address 24 Karat Street
City Platoid
State NY
Zip 00050
Email info@jewelware.com
Website www.jewelware.com
Telephone (609) 555-3344
Fax (609) 555-3345

Merchant ID 6
Merchant Name Crazy John’s Computers
Address 456 Enterprise St.
City Wynnsville
State CO
Zip 18888
Email crazy@merchandizing.net
Website www.merchandizing.net/crazy
Telephone (301) 555-9080
Fax n/a

Merchant ID 7
Merchant Name Collector’s Emporium
Address 256 Antiques Ave.
City Oldensurg
State PA
Zip 18999
Email findit@collectibles.net
Website www.collectibles.net
Telephone (570) 555-1608
Fax (570) 555-1609

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 6
Graded Project
 4. Use the Inventory form to populate the appropriate sections in the Inventory table
with the records listed below.

Inventory Records
Item ID 1
Item Name GameBox
Category Electronics
Manufacturer Super
Model GB928
Serial Number R729-382
Purchase Date 2/1/2017
Purchase Price $599.00
Merchant ID 1
Online Purchase No
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 2 years, parts only
Repair No
Repair Date (none)
Comments packaged with one free game control

Item ID 2
Item Name Smart TV
Category Electronics
Manufacturer Super
Model 4200
Serial Number 1930456
Purchase Date 2/20/2017
Purchase Price $499.00
Merchant ID 1
Online Purchase No
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 90 days, parts only
Repair No
Repair Date (none)
Comments (none)

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 7
Graded Project
 Item ID 3
Item Name Laptop
Category Electronics
Manufacturer Deluxe
Model SuperFast
Serial Number 879603-109-345
Purchase Date 2/27/2017
Purchase Price $899.00
Merchant ID 3
Online Purchase No
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 1 year, parts and labor
Repair Yes
Repair Date 6/1/2017
Comments keyboard replaced

Item ID 4
Item Name Bluetooth Headset
Category Electronics
Manufacturer Wireless
Model BT54910
Serial Number 345-896-000
Purchase Date 2/22/2017
Purchase Price $99.00
Merchant ID 4
Online Purchase Yes
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 12 months, parts only
Repair No
Repair Date (none)
Comments (none)

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 8
Graded Project
 Item ID 5
Item Name Ink Jet Printer
Category Electronics
Manufacturer Pro
Model U750
Serial Number 555639870
Purchase Date 1/15/2017
Purchase Price $49.00
Merchant ID 6
Online Purchase Yes
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 90 days, parts and tech support
Repair No
Repair Date (none)
Comments (none)

Item ID 6
Item Name Refrigerator
Category Appliances
Manufacturer Wonderlux
Model Ice Age 2000
Serial Number 2567590
Purchase Date 4/5/2017
Purchase Price $999.00
Merchant ID 2
Online Purchase No
Credit Card Purchase Yes
Warranty Type Manufacturer/store
Warranty Length 2 years, parts and labor/5 years, refrigeration parts
Repair No
Repair Date (none)
Comments (none)

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 9
Graded Project
 Item ID 7
Item Name Washing Machine
Category Appliances
Manufacturer Wonderlux
Model CL900
Serial Number 90050221
Purchase Date 6/4/2017
Purchase Price $625.00
Merchant ID 2
Online Purchase No
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 1 year, parts and labor
Repair No
Repair Date (none)
Comments (none)

Item ID 8
Item Name Clothes Dryer
Category Appliances
Manufacturer Wonderlux
Model DR199
Serial Number 199502211
Purchase Date 6/4/2017
Purchase Price $700.00
Merchant ID 2
Online Purchase No
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 1 year, parts and labor
Repair No
Repair Date (none)
Comments (none)

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 10
Graded Project
 Item ID 9
Item Name Dishwasher
Category Appliances
Manufacturer Washomatic
Model DW19
Serial Number 195-763984
Purchase Date 8/12/2017
Purchase Price $475.00
Merchant ID 3
Online Purchase No
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 1 year, parts and labor
Repair No
Repair Date (none)
Comments (none)

Item ID 10
Item Name Smart Phone
Category Electronics
Manufacturer EasyPhone
Model 17S
Serial Number 567-39QR4512
Purchase Date 1/30/2017
Purchase Price $799.00
Merchant ID 4
Online Purchase No
Credit Card Purchase Yes
Warranty Type Manufacturer
Warranty Length 1 year, parts only
Repair No
Repair Date (none)
Comments (none)

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 11
Graded Project
 Item ID 11
Item Name Heart Pendant on Gold Chain
Category Jewelry
Manufacturer GoldPlus
Model n/a
Serial Number n/a
Purchase Date 2/11/2017
Purchase Price $599.00
Merchant ID 5
Online Purchase No
Credit Card Purchase Yes
Warranty Type n/a
Warranty Length n/a
Repair No
Repair Date (none)
Comments 24 karat gold, 18” serpentine link chain

Item ID 12
Item Name Engagement Ring
Category Jewelry
Manufacturer Jewelserv
Model n/a
Serial Number n/a
Purchase Date 2/12/2017
Purchase Price $2,500.00
Merchant ID 5
Online Purchase No
Credit Card Purchase Yes
Warranty Type n/a
Warranty Length n/a
Repair No
Repair Date (none)
Comments Appraisal in safety deposit box

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 12
Graded Project
 Item ID 13
Item Name Super Hero #1 Comic Book
Category Collectibles
Manufacturer Funny Publishing May 1976 (publisher)
Model n/a
Serial Number n/a
Purchase Date 5/25/2017
Purchase Price $39.00
Merchant ID 7
Online Purchase Yes
Credit Card Purchase Yes
Warranty Type n/a
Warranty Length n/a
Repair No
Repair Date (none)
Comments 9.4 Comic Book Grading

Item ID 14
Item Name Super Hero #6 Comic Book
Category Collectibles
Manufacturer Funny Publishing November 1976 (publisher)
Model n/a
Serial Number n/a
Purchase Date 5/25/2017
Purchase Price $67.00
Merchant ID 7
Online Purchase Yes
Credit Card Purchase Yes
Warranty Type n/a
Warranty Length n/a
Repair No
Repair Date (none)
Comments 9.2 Comic Book Grading

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 13
Graded Project
QUERY THE DATABASE
1. Create a select query that retrieves the Item Name, Purchase Date, Purchase Price,
and Online Purchase fields from the Inventory table for items purchased online.

a. Have the select query sort the results in chronological order by

Purchase Date.

b. Be sure to format the Datasheet view so that all field names and data
are displayed entirely.

c. Save the query naming it “Online Purchases” and then close the query.

2. Create a select query that retrieves the Merchant Name from the Merchants table
and the Item Name, Category, Manufacturer, and Purchase Date from
the Inventory table for appliances.

a. Have the select query sort the results in alphabetical order by

Merchant Name.

b. Be sure to format the Datasheet view so that all field names and data
are displayed entirely.

c. Save the query naming it “Appliance Purchases” and then close the query.

CREATE REPORTS
1. Create a tabular report using the Online Purchases query.

a. Sort the report data by Purchase Date.

b. Print Preview your report and adjust formatting as necessary, making sure
all data are displayed on one page, as shown in Figure 3.

Figure 3

c. Save the report naming it “Online Purchases,” and then close the report.

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 14
Graded Project
 2. Create a tabular report using the Appliance Purchases query.

a. Reduce field widths so that all fields are displayed in portrait orientation.

b. Move the Page 1 of 1 footer so that it is centered below the report data.

c. Delete the record count and the summary line below the Merchant
Name data.

d. Group the report data by Merchant Name.

e. Sort the report data by Purchase Date.

f. Print Preview your report and adjust formatting as necessary, making sure
all data are displayed on one page, as shown in Figure 4.

Figure 4

g. Save the report naming it “Appliance Purchases,” and then close the report.

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 15
Graded Project
SCORING GUIDELINES

RUBRIC
SKILL/ NOT
EXEMPLARY PROFICIENT FAIR POOR
GRADING EVIDENT
(4) (3) (2) (1)
CRITERIA (0)
Tables with Tables with Tables with Tables with- No attempt
correct field mostly correct some correct out correct has been
Create names and field names field names field names made to
tables types have and types have and types and/or types create tables.
been created. been created. have been have been
created. created.
Forms N/A N/A An attempt No attempt
corresponding has been has been
to tables have made to made to
Create been created. create create forms.
forms forms, but
they don’t
correspond
to the tables.
All the Most of the Some of the Few of the No attempt
designed designed designed designed has been
records have records have records have records made to
Perform been entered been entered been entered have been perform data
data entry with minimal with minimal with minimal entered entry.
errors. errors. errors. with minimal
errors.
A select A select query A select query A select No attempt
query with the with correct cri- with correct query that has been
designated teria and some criteria and doesn’t made to
Create
fields and of the designed few of the contain the create a
a select correct fields has been designated correct select query.
query criteria has created. fields has criteria
been created. been created. has been
created.
A select A select query A select A filter No attempt
query with with the correct query with has been has been
Sort select the correct sort for the the wrong applied made to sort
query sort for the wrong field has sort has been to query query results.
results designed field been created. created. results in
has order to pro-
been created. vide a sort.

(Continued)

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 16
Graded Project
 All the field Some of the Few of the An attempt No attempt
names and field names field names has been to format
field data are and field data and field data made to Datasheet
Format
completely are completely are completely format view has
Datasheet displayed in displayed in displayed in Datasheet been made.
view select query select query select query view.
Datasheet view. Datasheet view. Datasheet
view.
A formatted A report with A format- Minimal No attempt
report with grouping and ted report effort has to create a
Create a grouping and sorting that’s with missing been made report has
report sorting has missing for- grouping and to group, been made.
been created. mats has been sorting has sort, and for-
created. been created. mat a report.

SUBMISSION CHECKLIST
Before submitting your project, make sure you’ve correctly completed the following steps:

q Create, save, and name an Access database.

q Create tables with appropriate field names and corresponding data types.

q Create formatted forms that correspond to tables.

q Use forms to populate a database with records.

q Create a select query using fields from one table.

q Create a select query using fields from multiple related tables.

q Designate query criteria for select query results.

q Designate a sort order for select query results.

q Format select query Datasheet view to completely display field names and field data.

q Create a report.

q Sort and group a report.

q Edit a report format.

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 17
Graded Project
SUBMISSION GUIDELINES
Each project is individually graded and therefore could take approximately 5–7 business
days to grade.

Follow this procedure to submit your assignment online:

1. Make sure the following information is in the heading of each file:

OO Your name

OO Your email address

OO Your student number

OO Course name and number

OO Project number (584806)

2. Go to http://www.pennfoster.edu and log in to your student portal.

3. On your student portal, click on Take Exam.

4. In the box provided, enter the examination number.

The number for this exam is 584806.

5. Click on Submit.

6. On the next screen, enter your email address. (Note: This information is required
for online submission.)

7. If you wish to tell your instructor anything specific regarding this assignment,
enter it in the Comments.

8. Attach your file or files as follows:

a. Click on the first Browse box.

b. Locate the file you wish to attach.

c. Double-click on the file.

d. To attach any additional files, click on the next Browse box and
repeat steps b and c. Repeat until all files are uploaded.

9. Click on Submit.

Be sure to keep a backup copy of any files you submit to the school!

MICROSOFT ACCESS
© PENN FOSTER, INC. 2016 2016 PAGE 18
Graded Project