create_and_save_a_new_blank_database_called_inventory

Create and save a new blank database called Inventory.accdb

as shown in . 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) (Figure 2)

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

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

• Input the inventory information listed at the end of this

booklet into your database. Use the current year for

dates marked 20XX.

• 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

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

• Input the merchant information listed at the end of this

booklet into your database.

After you’ve entered all the information into your database

tables, save the Inventory.accdb

Create a simple query on the Inventory table (Figure 4). 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

Create a multiple-table query on the Inventory and Merchants

tables. To create this query, you must establish a relationship

between the two tables (Figure 6). 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

Use the information listed below to create the Inventory database.

Item Number 1

Item Name: VCR

Item Category: Electronics

Item Manufacturer: Super

Item Model Number: VCR2000

Item Serial Number: 1001001001

Purchase Date: 02/01/20XX

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

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

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

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

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

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

Comments: ReplaceItem 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/20XX

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

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

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

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

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

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

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

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

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

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

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

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

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.

Use the information listed below to create the Merchants database.

Graded Project 17

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

Merchant Fax: (570) 555-1806