Advanced PC Access

dani82rose

Accessgradedproject-584108.pdf

Home >Computer Science homework help >Advanced PC Access

Microsoft Access Graded Project

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

READING ASSIGNMENT

Your project must be submitted as a Word document (.docx, .doc)*. 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 files contains the following information:

Your name Your student ID number The 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 files along with all documentation using the WinZip software program. To submit your graded project, follow these steps:

Go to http://www.pennfoster.edu (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, select View and then go to Design.

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

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

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

types:

http://www.pennfoster.edu

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

6. Save and then close the Inventory table.

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

8. 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 (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.

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 Records

Merchant ID 1

Merchant Name Electronics Mart

Address 65 Resister Ave.

City Blankston

State PA

Zip 18454

Email [email protected]

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)

Merchant ID 3

Merchant Name Stuff Mart

Address 721 Frengburg St.

City Shopville

State NY

Zip 10022

Email [email protected]

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 [email protected]

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 [email protected]

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 [email protected]

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 [email protected]

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

Manufacturer Super

Model GB928

Serial Number R729-382

Purchase Date 2/1/2020

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

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

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

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

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

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

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)

Item ID 9

Item Name Dishwasher

Category Appliances

Manufacturer Washomatic

Model DW19

Serial Number 195-763984

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

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

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

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

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)

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

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)

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.

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

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.

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.

58410800