Advanced PC Applications

GradedProject-MicrosoftAccess2.pdf

Home >Computer Science homework help >Advanced PC Applications

Graded Project: Microsoft Access

Your project must be submitted as a Microsoft Access document (.accdb). Your 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 !les contains the following information:

Your name

Your student ID number

"e lesson number (584046)

Your email address

Note: If you have more than 10 attachments, you’ll need to WinZip all of the project’s associated !les along with all documentation using the

WinZip software program.

To submit your graded project, follow these steps:

Go to http://www.pennfoster.edu.

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 !les 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, select View and then go to Design.

3. When prompted, name the new table "Inventory."

4. In Design view, change the !eld name 'ID' to 'Item ID' and verify that it’s data type AutoNumber and has been selected as the primary

key.

5. In Design view, add the remaining !elds and corresponding data types:

Listen

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 1 of 27

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

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 2 of 27

6. Save and then close the Inventory table.

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

8. Add the following !elds and corresponding data types. Be sure Merchant ID is the primary key:

Field Name Data Type Description

Merchant ID Number

Merchant Name Short Text

Address Short Text

City Short Text

State Short Text

Zip Short Text

Email Hyperlink

Website Hyperlink

Telephone Short Text (Phone number input mask).

Fax Short Text

9. Save and then close the Merchants table.

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

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 3 of 27

2. Create an Inventory form, with the purchase price as $0.00.

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

Merchant RecordsMerchant RecordsMerchant RecordsMerchant Records

Merchant ID 1

Merchant Name Electronics Mart

Address 65 Resister Ave.

City Blankston

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 4 of 27

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

Fax (none)

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 5 of 27

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

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 6 of 27

State PA

Zip 19019

Email callus@phonesandmore.com

Website www.phonesandmore.com

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

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 7 of 27

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

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 8 of 27

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 RecordsInventory RecordsInventory RecordsInventory Records

Item ID 1

Item Name GameBox

Category Electronics

Manufacturer Super

Model GB928

Serial Number R729-382

Purchase Date 2/1/2020

Purchase Price $599.00

Merchant ID 1

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 9 of 27

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/2020

Purchase Price $499.00

Merchant ID 1

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 10 of 27

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/2020

Purchase Price $899.00

Merchant ID 3

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 11 of 27

Online Purchase No

Credit Card Purchase Yes

Warranty Type Manufacturer

Warranty Length 1 year, parts and labor

Repair Yes

Repair Date 6/1/2020

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/2020

Purchase Price $99.00

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 12 of 27

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)

Item ID 5

Item Name Ink Jet Printer

Category Electronics

Manufacturer Pro

Model U750

Serial Number 555639870

Purchase Date 1/15/2020

Purchase Price $49.00

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 13 of 27

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/2020

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 14 of 27

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

Purchase Date 6/4/2020

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 15 of 27

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/2020

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 16 of 27

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)

Item ID 9

Item Name Dishwasher

Category Appliances

Manufacturer Washomatic

Model DW19

Serial Number 195-763984

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 17 of 27

Purchase Date 8/12/2020

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

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 18 of 27

Purchase Date 1/30/2020

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

Item Name Heart Pendant on Gold Chain

Category Jewelry

Manufacturer GoldPlus

Model n/a

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 19 of 27

Serial Number n/a

Purchase Date 2/11/2020

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

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 20 of 27

Serial Number n/a

Purchase Date 2/12/2020

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 Superhero #1 Comic Book

Category Collectibles

Manufacturer Funny Publishing May 1976 (publisher)

Model n/a

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 21 of 27

Serial Number n/a

Purchase Date 5/25/2020

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 Superhero #6 Comic Book

Category Collectibles

Manufacturer Funny Publishing November 1976 (publisher)

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 22 of 27

Model n/a

Serial Number n/a

Purchase Date 5/25/2020

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

Query the Database 1. Create a select query that retrieves the Item Name, Purchase Date, Purchase Price, and Online Purchase !elds 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 !eld 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.

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 23 of 27

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 !eld 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. Total the items by Purchase Price.

c. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page.

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

1. Create a tabular report using the Appliance Purchases query.

a. Reduce !eld widths so that all !elds 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. Delete the date and time in the report's header (if necessary).

e. Group the report data by merchant name.

f. Sort the report data by purchase date.

g. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page.

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 24 of 27

h. 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)

Percentage of Overall

Score

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.

24%

Create forms

Forms corresponding to tables have N/A N/A

An attempt has been made to create forms, but

No attempt has been made to 8%

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 25 of 27

been created. they don’t correspond to the tables.

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.

20%

Create an Appliances select query

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

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

A select query with correct criteria and a 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.

16%

Create an Online Purchases select query

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

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

A select query with correct criteria and a 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.

16%

Create an Appliances 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.

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 26 of 27

Create an Online Purchases 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 !eld 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 !elds from one table.

Create a select query using !elds 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 !eld names and !eld data.

Create a report.

Sort and group a report.

Edit a report format.

https://courses.portal2learn.com/d2l/le/enhancedSequenceVie…29799%2Factivity%2F1163112%3FfilterOnDatesAndDepth%3D1 3/28/26, 10:03 PM Page 27 of 27