create_and_save_a_new_blank_database_called_inventory

Create and save a new blank database called Inventory.accdb

as shown in . The
n, 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 Number2. Item Name3. Item Category4. 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 Name2. Item Category3. Item Manufacturer4. 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: NoCredit Card Purchase: YesWarranty 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: YesWarranty Type: Manufacturer

Warranty Length: 1 year parts/labor

/tech

support

Repair: NoRepair Date: (none)Comments: (none) 

Item Number: 4

Item Name: CD-ROM Drive

Item Category: ComputersItem Manufacturer: Deluxe

Item Model Number: CD200

Item Serial Number: 11233211

Purchase Date: 01/02/20XX

Purchase Price: $199.95Merchant ID: 6Online/Mail Order: YesCredit Card Purchase: YesWarranty Type: ManufacturerWarranty Length: 1 year parts/labor/tech

supportRepair: NoRepair 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: 6Online/Mail Order: YesCredit Card Purchase: YesWarranty Type: Manufacturer

Warranty Length: 90 days parts/labor/

tech support

Repair: Yes

Repair Date: 05/15/20XX

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

Purchase Price: $750.00

Merchant ID: 2

Online/Mail Order: NoCredit Card Purchase: Yes

Warranty Type: Manufacturer/store

Warranty Length: 90 days/1 year

parts/laborRepair: NoRepair 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: 2Online/Mail Order: NoCredit Card Purchase: YesWarranty Type: ManufacturerWarranty Length: 1 year parts/laborRepair: NoRepair Date: (none)Comments: (none) 

Item Number: 8

Item Name: Clothes Dryer

Item Category: AppliancesItem Manufacturer: General Appliance

Item Model Number: DR199

Item Serial Number: 199502211

Purchase Date: 01/19/20XX

Purchase Price: $599.95

Merchant ID: 2Online/Mail Order: NoCredit Card Purchase: YesWarranty Type: ManufacturerWarranty Length: 1 year parts/laborRepair: 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: NoCredit Card Purchase: YesWarranty Type: ManufacturerWarranty Length: 1 year parts/laborRepair: NoRepair 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: NoRepair 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.95Merchant ID: 4Online/Mail Order: NoCredit Card Purchase: NoWarranty Type: ManufacturerWarranty Length: 60 days parts/laborRepair: NoRepair Date: (none)Comments: (none)

Item Number: 12

Item Name: Zoom Lens

Item Category: Photo EquipmentItem Manufacturer: Camco

Item Model Number: Zoom10

Item Serial Number: 351

Purchase Date: 02/20/20XX

Purchase Price: $125.00

Merchant ID: 4Online/Mail Order: NoCredit Card Purchase: NoWarranty 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: NoCredit Card Purchase: Yes

Warranty Type: n/a

Warranty Length: n/a

Repair: NoRepair 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/aItem Serial Number: n/a

Purchase Date: 02/11/20XX

Purchase Price: $1,500.00

Merchant ID: 5Online/Mail Order: NoCredit Card Purchase: YesWarranty Type: n/aWarranty Length: n/aRepair: NoRepair 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: YesCredit Card Purchase: YesWarranty Type: n/aWarranty Length: n/aRepair: NoRepair 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: 7Online/Mail Order: YesCredit Card Purchase: YesWarranty Type: n/aWarranty Length: n/aRepair: NoRepair 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:

emart@blanknet.com

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: BlankstonMerchant State: PAMerchant 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: custserv@stuffmart.com

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: photobarn@snapshot.com

Merchant E-mail: photobarn@snapshot.com

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: info@jewelware.com

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: crazy@merchandizing.net

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/aMerchant URL: n/a

Merchant Phone: (570) 555-1608

Merchant Fax: (570) 555-1806