GRADED PROJECT

In this graded project, you’ll create a home inventory database based upon fictitious information provided in this booklet. Using this data, you’ll also perform queries and create reports. Your Access project will consist of the following items:

 

 

1. Two data tables

2. Two data entry forms

3. Simple query and report

4. Multiple-table query and report

 

Creating a Database

Create and save a new blank database called Inventory.accdb. Then, use the following steps to work with your database.

 

Create the table “Inventory” in Design View with the following data fields:

1. Item Number (auto-generated)

2. Item Name (such as computer or washing machine)

3. Item Category (such as Appliances or Electronics)

4. Item Manufacturer

5. Item Model Number

6. Item Serial Number

7. Purchase Date

8. Purchase Price

9. Merchant ID

10. Online/Mail Order (Yes/No)

11. Credit Card Purchase (Yes/No)

12. Warranty Type (store, manufacturer, both)

13. Warranty Length

14. Repair (Yes/No)

15. Repair Date

16. Comments

 

Set the Item Number field as the primary key (right click Item Number)

 

Create a new form using the Form Wizard. The form is based upon the Inventory table, titled “Inventory.” Use all the fields from the Inventory table. The form should possess the Columnar layout.

 

Item Number 1

Item Name: VCR

Item Category: Electronics

Item Manufacturer: Super

Item Model Number: VCR2000

Item Serial Number: 1001001001

Purchase Date: 02/01/2016

Purchase Price: $199.95

Merchant ID: 1

Online/Mail Order: No

Credit Card Purchase: Yes

Warranty Type: Manufacturer

Warranty Length: 60 days parts/labor

Repair: No

Repair Date: (none)

Comments: (none)

 

Item Number: 2

Item Name: Television

Item Category: Electronics

Item Manufacturer: Telecom

Item Model Number: TV100

Item Serial Number: 001001001

Purchase Date: 02/14/2016

Purchase Price: $299.95

Merchant ID: 1

Online/Mail Order: No

Credit Card Purchase: Yes

Warranty Type: Manufacturer

Warranty Length: 90 days parts/labor

Repair: Yes

Repair Date: 09/01/2016

Comments: Remove candy from controls

 

Item Number: 3

Item Name: Personal Computer

Item Category: Computers

Item Manufacturer: Deluxe

Item Model Number: PC1000

Item Serial Number: 100200100

Purchase Date: 02/01/2016

Purchase Price: $1,299.95

Merchant ID: 6

Online/Mail Order: Yes

Credit Card Purchase: Yes

Warranty Type: Manufacturer

Warranty Length: 1 year parts/labor/tech support

Repair: No

Repair Date: (none)

Comments: (none)

 

Item Number: 4

Item Name: CD-ROM Drive

Item Category: Computers

Item Manufacturer: Deluxe

Item Model Number: CD200

Item Serial Number: 11233211

Purchase Date: 01/02/2016

Purchase Price: $199.95

Merchant ID: 6

Online/Mail Order: Yes

Credit Card Purchase: Yes

Warranty Type: Manufacturer

Warranty Length: 1 year parts/labor/tech support

Repair: No

Repair Date: (none)

Comments: (none)

 

Item Number: 5

Item Name: Ink-Jet Printer

Item Category: Computers

Item Manufacturer: Stylus

Item Model Number: IJ750

Item Serial Number: 555222123

Purchase Date: 01/02/2016

Purchase Price: $169.95

Merchant ID: 6

Online/Mail Order: Yes

Credit Card Purchase: Yes

Warranty Type: Manufacturer

Warranty Length: 90 days parts/labor/ tech support

Repair: Yes

Repair Date: 05/15/2016

Comments: Replace defective print head

 

Item Number: 6

Item Name: Refrigerator

Item Category: Appliances

Item Manufacturer: Wonderlux

Item Model Number: Deep Freeze 250

Item Serial Number: 2505521

Purchase Date: 04/01/2016

Purchase Price: $750.00

Merchant ID: 2

Online/Mail Order: No

Credit Card Purchase: Yes

Warranty Type: Manufacturer/store

Warranty Length: 90 days/1 year parts/labor

Repair: No

Repair Date: (none)

Comments: (none)

 

Item Number: 7

Item Name: Washing Machine

Item Category: Appliances

Item Manufacturer: General Appliance

Item Model Number: CL900

Item Serial Number: 90050221

Purchase Date: 12/19/2016

Purchase Price: $450.00

Merchant ID: 2

Online/Mail Order: No

Credit Card Purchase: Yes

Warranty Type: Manufacturer

Warranty Length: 1 year parts/labor

Repair: No

Repair Date: (none)

Comments: (none)

 

Item Number: 8

Item Name: Clothes Dryer

Item Category: Appliances

Item Manufacturer: General Appliance

Item Model Number: DR199

Item Serial Number: 199502211

Purchase Date: 01/19/2016

Purchase Price: $599.95

Merchant ID: 2

Online/Mail Order: No

Credit Card Purchase: Yes

Warranty Type: Manufacturer

Warranty Length: 1 year parts/labor

Repair: Yes

Repair Date: 06/09/2016

Comments: Replaced gas nozzle

 

Item Number: 9

Item Name: Dishwasher

Item Category: Appliances

Item Manufacturer: Washmatic

Item Model Number: DW19

Item Serial Number: 19578921101

Purchase Date: 08/12/2016

Purchase Price: $275.00

Merchant ID: 3

Online/Mail Order: No

Credit Card Purchase: Yes

Warranty Type: Manufacturer

Warranty Length: 1 year parts/labor

Repair: No

Repair Date: (none)

Comments: (none)

 

Item Number: 10

Item Name: Digital Camera

Item Category: Computers

Item Manufacturer: Snapic

Item Model Number: Snap100

Item Serial Number: 1002020

Purchase Date: 01/02/2016

Purchase Price: $225.00

Merchant ID: 4

Online/Mail Order: No

Credit Card Purchase: No

Warranty Type: Manufacturer

Warranty Length: 60 days parts/labor

Repair: No

Repair Date: (none)

Comments: (none)

 

Item Number: 11

Item Name: 35mm Camera

Item Category: Photo Equipment

Item Manufacturer: Camco

Item Model Number: SLR75

Item Serial Number: 751000023

Purchase Date: 05/20/2016

Purchase Price: $199.95

Merchant ID: 4

Online/Mail Order: No

Credit Card Purchase: No

Warranty Type: Manufacturer

Warranty Length: 60 days parts/labor

Repair: No

Repair Date: (none)

Comments: (none)

 

Item Number: 12

Item Name: Zoom Lens

Item Category: Photo Equipment

Item Manufacturer: Camco

Item Model Number: Zoom10

Item Serial Number: 351

Purchase Date: 02/20/2016

Purchase Price: $125.00

Merchant ID: 4

Online/Mail Order: No

Credit Card Purchase: No

Warranty Type: Manufacturer

Warranty Length: 30 days parts/labor

Repair: Yes

Repair Date: 03/10/2016

Comments: Replaced defective “C” mount

 

Item Number: 13

Item Name: Gold Chain

Item Category: Jewelry

Item Manufacturer: Goldstuff

Item Model Number: n/a

Item Serial Number: n/a

Purchase Date: 02/12/2016

Purchase Price: $500.00

Merchant ID: 5

Online/Mail Order: No

Credit Card Purchase: Yes

Warranty Type: n/a

Warranty Length: n/a Repair: No

Repair Date: (none)

Comments: 24-karat gold, 24† long, serpentine links

 

Item Number: 14 Item Name: Engagement Ring

Item Category: Jewelry

Item Manufacturer: Jewelserv

Item Model Number: n/a

Item Serial Number: n/a

Purchase Date: 02/11/2016

Purchase Price: $1,500.00

Merchant ID: 5

Online/Mail Order: No

Credit Card Purchase: Yes

Warranty Type: n/a

Warranty Length: n/a

Repair: No

Repair Date: (none)

Comments: 3/4-carat square cut diamond in 18-karat gold setting

 

Item Number: 15

Item Name: Lighter

Item Category: Collectibles

Item Manufacturer: ZIPpy

Item Model Number: 21BT200

Item Serial Number: n/a

Purchase Date: 09/25/2016

Purchase Price: $100.00

Merchant ID: 7

Online/Mail Order: Yes

Credit Card Purchase: Yes

Warranty Type: n/a

Warranty Length: n/a

Repair: No

Repair Date: (none)

Comments: Original 1950 commemorative edition. #200 of 500 made. Appraised at $200.00.

 

Item Number: 16

Item Name: Candy Dish

Item Category: Collectibles

Item Manufacturer: Jones Glass

Item Category: n/a

Item Serial Number: n/a

Purchase Date: 12/20/2016

Purchase Price: $250.00

Merchant ID: 7

Online/Mail Order: Yes

Credit Card Purchase: Yes

Warranty Type: n/a

Warranty Length: n/a

Repair: No

Repair Date: (none)

Comments: Cranberry Depression glass candy dish. Appraised at $500.00.

 

Create the table “Merchants” with the following data fields:

1. Merchant ID

2. Merchant Name

3. Merchant Address

4. Merchant City

5. Merchant State

6. Merchant ZIP

7. Merchant E-Mail

8. Merchant URL

9. Merchant Phone

10. Merchant Fax

 

Merchant ID: 1

Merchant Name: Electronics Mart

Merchant Address: 65 Resistor Ave.

Merchant City: Blankston

Merchant State: PA

Merchant ZIP: 18454

Merchant E-mail: [email protected]

Merchant URL: http://www.emart.com

Merchant Phone: (570) 555-1111

Merchant Fax: (570) 555-1112

 

Merchant ID: 2

Merchant Name: Appliances Inc.

Merchant Address: 2020 Mechanics Road

Merchant City: Blankston

Merchant State: PA

Merchant ZIP: 18454

Merchant E-mail: n/a

Merchant URL: n/a

Merchant Phone: (570) 555-1234

Merchant Fax: (570) 555-1235

 

Merchant ID: 3

Merchant Name: Stuff Mart

Merchant Address: 721 Frengburg St.

Merchant City: Shopville

Merchant State: NY

Merchant ZIP: 10022

Merchant E-mail: [email protected]

Merchant URL: http://www.stuffmart.com

Merchant Phone: (212) 555-5432

Merchant Fax: (212) 555-6543

 

Merchant ID: 4

Merchant Name: Photo Barn

Merchant Address: 21 Framer Circle

Merchant City: Klossville

Merchant State: PA

Merchant ZIP: 19019

Merchant E-mail: [email protected]

Merchant E-mail: [email protected]

Merchant URL: http://www.snapshot.com /photobarn

Merchant Phone: (612) 555-9876

Merchant Fax: (612) 555-8765

 

Merchant ID: 5

Merchant Name: Jewelry Warehouse

Merchant Address: 24 Karat St.

Merchant City: Platold

Merchant State: NJ

Merchant ZIP: 00050

Merchant E-mail: [email protected]

Merchant URL: http://www.jewelware.com

Merchant Phone: (609) 555-6754

Merchant Fax: (609) 555-2212

 

Merchant ID: 6

Merchant Name: Crazy John’s Computers

Merchant Address: 456 Enterprise St.

Merchant City: Wynnsville

Merchant State: CO

Merchant ZIP: 18888

Merchant E-mail: [email protected]

Merchant URL: http://www.merchandizing .net/crazy

Merchant Phone: (301) 555-9080

Merchant Fax: (301) 555-8090

 

Merchant ID: 7

Merchant Name: Collector’s Emporium

Merchant Address: 256 Antiques Ave.

Merchant City: Oldenburg

Merchant State: PA

Merchant ZIP: 18999

Merchant E-mail: n/a

Merchant URL: n/a

Merchant Phone: (570) 555-1608

 

Set the Merchant ID field as the primary key.

 

Create a new form using the Form Wizard and based upon the Merchants table, titled “Merchants.” Use all the fields from the Merchants table. The form should possess the Columnar layout.

 

After you’ve entered all the information into your database tables, save the Inventory.accdb file. If you have a printer, print a hard copy of your inventory table and a copy of your data entry form. Check your work for errors. Your project grade will be based in part on the accuracy of your work.

 

Creating a Simple Database Query

Create a simple query on the Inventory table. The query should include data entries only for items that have been repaired. Include the following fields:

1. Item Number

2. Item Name

3. Item Category

4. Item Manufacturer.

5. Purchase Date

6. Warranty Type

7. Warranty Length

8. Repair

9. Repair Date

10. Comments

The data within the query should be sorted alphabetically by Item Category. Save the query as Repaired Items.

 

Based on the data contained in the query Repaired Items, create a report with the Report Wizard using all the fields from the query. No grouping levels are required in this report. Sort in ascending order by Item Name (Figure 5). The report should have the following format: • Columnar layout • Portrait orientation Title the report Repaired Items List. Make sure all of the fields show clearly in the report, including the field titles. After you’ve completed the simple query and report, resave the Inventory.accdb file onto the disk and your hard drive. If you have a printer, print a hard copy of your simple query and report. Again, check your work carefully for errors, because the project grade will be based in part on the accuracy of your work.

 

Creating a Multiple-Database Query

Create a multiple-table query on the Inventory and Merchants tables. To create this query, you must establish a relationship between the two tables. The common field between the tables is Merchant ID. Both fields should be set to text in their respective tables. Now you may begin to build your query. Include the following fields from the Inventory table: 1. Item Name 2. Item Category 3. Item Manufacturer 4. Purchase Date

 

Include the following fields from the Merchants table: 1. Merchant Name 2. Merchant Address 3. Merchant City 4. Merchant State 5. Merchant ZIP The data within the query should be sorted alphabetically by Merchant Name. Set the criteria cell for the Item Category field to search for “Appliances.” Save the query as Appliance Store Purchases. Based on the data contained in the Appliance Store Purchases query, create a report with the Report Wizard using the following fields from the query (Figure 7): 1. Item Name 2. Item Category 3. Item Manufacturer 4. Purchase Date 5. Merchant Name 6. Merchant Address 7. Merchant City 8. Merchant State 9. Merchant ZIP

 

View the data by Merchants. No grouping levels are required in this report. Sort detail records in ascending order by Purchase Date. Format the report as follows: • Stepped • Landscape orientation Title the report Appliance Store Purchases List. Make sure all the fields show clearly in the report, including the field titles

 

After you’ve completed the multiple-table query and report, resave the Inventory.accdb file onto the disk and your hard drive. If you have a printer, print a hard copy of your simple query and report. Again, check your work carefully for errors.