Appliance Store Database

INTRODUCTION

Now that youve completed the Microsoft Access study unit, youre ready to complete your graded project. As you work through this project, youll apply your recently learned Access skills to create and use a home inventory database.

A home inventory database can serve many useful applications in todays society. It allows you to access important information regarding your purchases without having to sort through a paper file of merchant receipts. This will minimize damage and loss of important receipts, which you may need to produce to get warranty service. Keeping a copy of your database offsite, such as in a safety-deposit box or with family or friends, will allow you to hand a detailed printout of your possessions to an insurance adjuster in case of theft, fire, or other disaster. Over time, you may wish to add more tables to the database for different purposes. For example, you may use the Make Table Query function in Access to subdivide your database by Item Category.

GRADED PROJECT

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

• Two data tables
• Two data entry forms
• Simple query and report
• Multiple-table query and report

Creating a Database

Create and save a new blank database called inventory.accdb as shown in Figure 1. Then, use the following steps to work with your database. (Your new database screen will appear slightly different than the figure, depending on the files already on your computer.) See image.

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

• Item Number (auto-generated)
• Item Name (such as computer or washing machine)
• Item Category (such as Appliances or Electronics)
• Item Manufacturer
• Item Model Number
• Item Serial Number
• Purchase Date
• Purchase Price
• Merchant ID
• Online/Mail Order (Yes/No)
• Credit Card Purchase (Yes/No)
• Warranty Type (store, manufacturer, both)
• Warranty Length
• Repair (Yes/No)
• Repair Date
• Comments

Set the Item Number field as the primary key (Figure 3). See image. See figure 3 image.

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 and Solstice theme.

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:

• Merchant ID
• Merchant Name
• Merchant Address
• Merchant City
• Merchant State
• Merchant ZIP
• Merchant E-mail
• Merchant URL
• Merchant Phone
• 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 youve 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 (Figure 4). The query should include data entries only for items that have been repaired. Include the following fields:

• Item Number
• Item Name
• Item Category
• Item Manufacturer
• Purchase Date
• Warranty Type
• Warranty Length
• Repair
• Repair Date
• Comments

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

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 youve 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. See image.

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 (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:

• Item Name
• Item Category
• Item Manufacturer
• Purchase Date See image.

Include the following fields from the Merchants table:

• Merchant Name
• Merchant Address
• Merchant City
• Merchant State
• 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):

• Item Name
• Item Category
• Item Manufacturer
• Purchase Date
• Merchant Name
• Merchant Address
• Merchant City
• Merchant State
• Merchant ZIP See image.

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 (Figure 8). See image.

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