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 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.)
• 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
• 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. MerchantCity
5. MerchantState
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 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:
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.
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
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).
