Access

Advanced PC Applications : Microsoft Access Graded Project

Lesson 2 Overview

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 would 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’ll store off site in

case of emergency.

2.1 Create a home inventory database that can be referred to in case of damage, theft, or natural disaster Graded Project: Microsoft Access 2016

READING ASSIGNMENT

Page 1Copyright Penn Foster, Inc. 2019 Course Version: 1

Your project must be submitted as a Microsoft Access document (.accdb). The project will be individually graded by your instructor and therefore will take up to a few weeks to grade. Be sure that each of your files contains the following information:

Your name Your student ID number The lesson number (######00) Your email address

Note: If you have more than 10 attachments, you’ll need to WinZip all of the project’s associated files along with all documentation using the WinZip software program. To submit your graded project, follow these steps:

Go to http://www.pennfoster.edu. Log in to your student portal. Click on Take Exam next to the lesson you’re working on. Follow the instructions provided to complete your exam.

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

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’s data type AutoNumber and has been selected as the

primary key.

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

types:

Page 2Copyright Penn Foster, Inc. 2019 Course Version: 1

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

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:

Page 3Copyright Penn Foster, Inc. 2019 Course Version: 1

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 the figure below.

Page 4Copyright Penn Foster, Inc. 2019 Course Version: 1

[An image of a Merchants form]

2. Create an Inventory form that looks similar to the figure below.

[An image of an Inventory form]

3. Use the Merchants form to populate the appropriate sections in

the Merchants table with the records listed below.

Page 5Copyright Penn Foster, Inc. 2019 Course Version: 1

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

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

Page 6Copyright Penn Foster, Inc. 2019 Course Version: 1

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

Page 7Copyright Penn Foster, Inc. 2019 Course Version: 1

Telephone (612) 555-9876

Fax n/a

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

Page 8Copyright Penn Foster, Inc. 2019 Course Version: 1

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

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

Page 9Copyright Penn Foster, Inc. 2019 Course Version: 1

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

Page 10Copyright Penn Foster, Inc. 2019 Course Version: 1

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)

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

Page 11Copyright Penn Foster, Inc. 2019 Course Version: 1

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)

Page 12Copyright Penn Foster, Inc. 2019 Course Version: 1

Comments (none)

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

Page 13Copyright Penn Foster, Inc. 2019 Course Version: 1

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)

Item ID 7

Item Name Washing Machine

Category Appliances

Manufacturer Wonderlux

Model CL900

Serial Number 90050221

Page 14Copyright Penn Foster, Inc. 2019 Course Version: 1

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

Page 15Copyright Penn Foster, Inc. 2019 Course Version: 1

Credit Card Purchase Yes

Warranty Type Manufacturer

Warranty Length 1 year, parts and labor

Repair No

Repair Date (none)

Comments (none)

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

Page 16Copyright Penn Foster, Inc. 2019 Course Version: 1

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)

Item ID 11

Page 17Copyright Penn Foster, Inc. 2019 Course Version: 1

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

Page 18Copyright Penn Foster, Inc. 2019 Course Version: 1

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

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

Page 19Copyright Penn Foster, Inc. 2019 Course Version: 1

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

Page 20Copyright Penn Foster, Inc. 2019 Course Version: 1

Repair No

Repair Date (none)

Comments 9.2 Comic Book Grading

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

Page 21Copyright Penn Foster, Inc. 2019 Course Version: 1

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 the figure.

[An image of the print preview of the Online Purchases report]

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

close the report.

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’s 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

Page 22Copyright Penn Foster, Inc. 2019 Course Version: 1

necessary, making sure all data are displayed on one page,

as shown in the figure.

[An image of the print preview for the Appliance Purchases report]

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

close the report.

Scoring Guidelines

Rubric

SKILL/GRADING CRITERIA

EXEMPLARY (4)

PROFICIENT (3)

FAIR (2)

POOR (1)

NOT EVIDENT

(0)

Create tables

Tables with correct field names and types have been created.

Tables with mostly correct field names and types have been created.

Tables with some correct field names and types have been created.

Tables without correct field names and/or types have been created.

No attempt has been made to create tables.

Page 23Copyright Penn Foster, Inc. 2019 Course Version: 1

Create forms Forms corresponding to tables have been created.

N/A N/A

An attempt has been made to create forms, but they don’t correspond to the tables.

No attempt has been made to create forms.

Perform data entry

All the designated records have been entered with minimal errors.

Most of the designated records have been entered with minimal errors.

Some of the designated records have been entered with minimal errors.

Few of the designated records have been entered with minimal errors.

No attempt has been made to perform data entry.

Create a select query

A select query with the designated fields and correct criteria has been created.

A select query with correct criteria and some of the designated fields has been created.

A select query with correct criteria and few of the designated fields has been created.

A select query that doesn’t contain the correct criteria has been created.

No attempt has been made to create a select query.

Sort select query results

A select query with the correct sort for the designated field has been created.

A select query with the correct sort for the wrong field has been created.

A select query with the wrong sort has been created.

A filter has been applied to query results to provide a sort.

No attempt has been made to sort query results.

Format Datasheet view

All the field names and field data are completely displayed in select query Datasheet view.

Some of the field names and field data are completely displayed in select query Datasheet view.

Few of the field names and field data are completely displayed in select query Datasheet view.

An attempt has been made to format Datasheet view.

No attempt to format Datasheet view has been made.

Page 24Copyright Penn Foster, Inc. 2019 Course Version: 1

Create a report

A formatted report with grouping and sorting has been created.

A report with grouping and sorting that’s missing formats has been created.

A formatted report with missing grouping and sorting has been created.

Minimal effort has been made to group, sort, and format a report.

No attempt to create a report has been made.

Submission Checklist

Before submitting your project, make sure you’ve correctly completed

the following steps:

Create, save, and name an Access database.

Create tables with appropriate field names and corresponding

data types.

Create formatted forms that correspond to tables.

Use forms to populate a database with records.

Create a select query using fields from one table.

Create a select query using fields from multiple related tables.

Designate query criteria for select query results.

Designate a sort order for select query results.

Format select query Datasheet view to completely display field

names and field data.

Create a report.

Sort and group a report.

Edit a report format.

Page 25Copyright Penn Foster, Inc. 2019 Course Version: 1

Page 26Copyright Penn Foster, Inc. 2019 Course Version: 1