GRADED PROJECT 1 Creating a Database
INTRODUCTION
Now that you've completed the Microsoft Access study unit, you're ready to complete your graded project. As you work through this project, you'll apply your recently learned Access skills to create and use a home inventory database. A home inventory database can serve many useful applications in today's 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 Table Query function in Access to subdivide your database by item category.
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
1 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.) FIGURE 1—Save the new database as Inventory.accdb. • 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 2 Microsoft Access FIGURE 2—Use the AutoNumber feature for the Item Number field. 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 (Figure 3). Graded Project 3 FIGURE 3—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 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.
Create a query on the Inventory table for 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. 6 Microsoft Access FIGURE 5—Sort the Repaired Items List report by Item Name. 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:
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
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 (Figure 8). Graded Project 9 FIGURE 8—Appliance Store Purchases List
Report
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. 10 Microsoft Access INSPECTING YOUR WORK AND
SENDING IT TO THE SCHOOL Congratulations! Now that you've completed the Access project, you're ready to apply all you've learned about presentation graphics in your own enterprises. All you have left to do with the project is to inspect your completed file one last time and send it to the school for grading. Inspecting Your Completed Project Proofread your work. Check your files for errors. Your project grade will be based on the accuracy with which you entered
and edited the information. Make sure your final project is free of errors, and that you closely followed the instructions.
Be sure to send the file named Inventory.accdb. Your instructor will grade your project based on the following guidelines:
Inventory Table 25 points _____ Single Database Query and Report 25 points _____ Merchants Table Multiple-Database Query and Report 12 25 points
25 points _____
_____ Microsoft Access Total _____ Item Number 1 Item Category: VCR Item Model Number: Super Purchase Date: 1001001001 Item Name: Item Manufacturer: Electronics Item Serial Number: VCR2000 Purchase Price: 02/01/20XX Online/Mail Order: 1 Warranty Type: Yes Repair: 60 days parts/labor Comments: (none) Merchant ID: $199.95 Credit Card Purchase: No Warranty Length: Manufacturer Repair Date: No (none) Item Number: 2 Item Category: Television Item Model Number: Telecom Purchase Date: 001001001 Merchant ID: $299.95 Credit Card Purchase: No Warranty Length: Manufacturer Repair Date: Yes Item Name: Item Manufacturer: Electronics Item Serial Number: TV100 Purchase Price: 02/14/20XX Online/Mail Order: 1 Warranty Type: Yes Repair: 90 days parts/labor Comments: 09/01/20XX Graded Project Remove candy from
controls Item Number: 3 Item Category: Personal Computer Item Model Number: Deluxe Purchase Date: 100200100 Merchant ID: $1,299.95 Credit Card Purchase: Yes Warranty Length: Manufacturer Item Name: Item Manufacturer: Computers Item Serial Number: PC1000 Purchase Price: 02/01/20XX Online/Mail Order: 6 Warranty Type: Yes Repair: Repair Date:
Comments: 1 year parts/labor/tech
support
No
(none)
(none) Item Number: 4 Item Category: CD-ROM Drive Item Model Number: Deluxe Purchase Date: 11233211 Merchant ID: $199.95 Credit Card Purchase: Yes Warranty Length: Manufacturer Item Name: Item Manufacturer: Computers Item Serial Number: CD200 Purchase Price: 01/02/20XX Online/Mail Order: 6 Warranty Type: Yes Repair: Repair Date:
Comments: 1 year parts/labor/tech
support
No
(none)
(none) 13 Item Number: 5 Item Category: Ink-Jet Printer Item Model Number: Stylus Purchase Date: 555222123 Merchant ID: $169.95 Credit Card Purchase: Yes Warranty Length: Manufacturer Item Name: Item Manufacturer: Computers Item Serial Number: IJ750 Purchase Price: 01/02/20XX Online/Mail Order: 6 Warranty Type: Yes Repair: Repair Date: 90 days parts/labor/
tech support
Yes Comments: 05/15/20XX Item Number: 6 Item Category: Refrigerator Item Model Number: Wonderlux Purchase Date: 2505521 Merchant ID: $750.00 Credit Card Purchase: No Warranty Length: Manufacturer/store Item Name: Replace defective print
head Item Manufacturer: Appliances Item Serial Number: Deep Freeze 250 Purchase Price: 04/01/20XX Online/Mail Order: 2 Warranty Type: Yes Repair: Repair Date:
Comments: 14 90 days/1 year
parts/labor
No Item Number: 7 Item Category: Washing Machine Item Model Number: General Appliance Purchase Date: 90050221 Merchant ID: $450.00 Credit Card Purchase: No Warranty Length: Manufacturer Repair Date: No Item Name: Item Manufacturer: Appliances Item Serial Number: CL900 Purchase Price: 12/19/20XX Online/Mail Order: 2 Warranty Type: Yes Repair: 1 year parts/labor Comments: (none)
(none) Item Number: 8 Item Category: Clothes Dryer Item Model Number: General Appliance Purchase Date: 199502211 Merchant ID: $599.95 Credit Card Purchase: No Warranty Length: Manufacturer Repair Date: Yes Item Name: Item Manufacturer: Appliances Item Serial Number: DR199 Purchase Price: 01/19/20XX Online/Mail Order: 2 Warranty Type: Yes Repair: 1 year parts/labor Comments: 06/09/20XX
Replaced gas nozzle (none)
(none) Microsoft Access Item Number: 9 Item Category: Dishwasher Item Model Number: Washmatic Purchase Date: 19578921101 Merchant ID: $275.00 Credit Card Purchase: No Warranty Length: Manufacturer Repair Date: No Item Name: Item Manufacturer: Appliances Item Serial Number: DW19 Purchase Price: 08/12/20XX Online/Mail Order: 3 Warranty Type: Yes Repair: 1 year parts/labor Comments: (none)
(none) Item Number: 10 Item Category: Digital Camera Item Model Number: Snapic Purchase Date: 1002020 Merchant ID: $225.00 Credit Card Purchase: No Warranty Length: Manufacturer Repair Date: No Item Name: Item Manufacturer: Computers Item Serial Number: Snap100 Purchase Price: 01/02/20XX Online/Mail Order: 4 Warranty Type: No Repair: 60 days parts/labor Comments: (none) Graded Project (none) Item Number: 11 Item Category: 35mm Camera Item Model Number: Camco Purchase Date: 751000023 Merchant ID: $199.95 Credit Card Purchase: No Warranty Length: Manufacturer Repair Date: No Item Name: Item Manufacturer: Photo Equipment Item Serial Number: SLR75 Purchase Price: 05/20/20XX Online/Mail Order: 4 Warranty Type: No Repair: 60 days parts/labor Comments: (none)
(none) Item Number: 12 Item Category: Zoom Lens Item Model Number: Camco Purchase Date: 351 Merchant ID: $125.00 Credit Card Purchase: No Warranty Length: Manufacturer Repair Date: Yes Item Name: Item Manufacturer: Photo Equipment Item Serial Number: Zoom10 Purchase Price: 02/20/20XX Online/Mail Order: 4 Warranty Type: No Repair: 30 days parts/labor Comments: 03/10/20XX
Replaced defective
"C" mount 15 Item Number: 13 Item Category: Gold Chain Item Model Number: Goldstuff Purchase Date: n/a Merchant ID: $500.00 Credit Card Purchase: No Warranty Length: n/a Repair Date: No Item Name: Item Manufacturer: Jewelry Item Serial Number: n/a Purchase Price: 02/12/20XX Online/Mail Order: 5 Warranty Type: Yes Repair: n/a Comments: (none) Item Number: 14 Item Category: Engagement Ring Item Model Number: Jewelserv Purchase Date: n/a Merchant ID: $1,500.00 Credit Card Purchase: No Warranty Length: n/a Repair Date: No Item Name: 24-karat gold, 24† long,
serpentine links Item Manufacturer: Jewelry Item Serial Number: n/a Purchase Price: 02/11/20XX Online/Mail Order: 5 Warranty Type: Yes Repair: n/a Comments: (none) 16 3/ 4-carat square cut
diamond in 18-karat
gold setting Item Number: 15 Item Category: Lighter Item Model Number: ZIPpy Purchase Date: n/a Merchant ID: $100.00 Credit Card Purchase: Yes Warranty Length: n/a Repair Date: No Item Name: Item Manufacturer: Collectibles Item Serial Number: 21BT200 Purchase Price: 09/25/20XX Online/Mail Order: 7 Warranty Type: Yes Repair: n/a Comments: (none) Item Number: 16 Item Category: Candy Dish Item Category: Jones Glass Purchase Date: n/a Merchant ID: $250.00 Credit Card Purchase: Yes Warranty Length: n/a Repair Date: No Item Name: Original 1950
commemorative edition.
#200 of 500 made.
Appraised at $200.00. Item Manufacturer: Collectibles Item Serial Number: n/a Purchase Price: 12/20/20XX Online/Mail Order: 7 Warranty Type: Yes Repair: n/a Comments: (none)
Cranberry Depression
glass candy dish.
Appraised at $500.00. Microsoft Access Merchant ID: Merchant Name: Merchant Address: 1 Electronics Mart Merchant City: 65 Resistor Ave. Merchant ZIP: PA Merchant URL: emart@blanknet.com Merchant Fax: (570) 555-1111 Merchant State: Blankston Merchant E-mail: 18454 Merchant Phone: http://www.emart.com (570) 555-1112 Merchant ID: 2 Merchant Address:
Merchant City: 2020 Mechanics Road Merchant ZIP: PA Merchant URL: n/a Merchant Fax: (570) 555-1234 Merchant Name: Appliances Inc. Merchant State: Blankston Merchant E-mail: 18454 Merchant Phone: n/a (570) 555-1235 Merchant ID: 3 Merchant Address:
Merchant City: 721 Frengburg St. Merchant ZIP: NY Merchant URL: custserv@stuffmart.com Merchant Fax: (212) 555-5432 Merchant Name: Stuff Mart Merchant State: Shopville Merchant E-mail: 10022 Merchant Phone: http://www.stuffmart.com (212) 555-6543 Merchant ID: 4 Merchant Address:
Merchant City: 21 Framer Circle Merchant ZIP: PA Merchant Name:
Merchant State: Graded Project Photo Barn Klossville 19019 Merchant E-mail: Merchant E-mail:
Merchant URL: Merchant Phone: photobarn@snapshot.com
photobarn@snapshot.com
http://www.snapshot.com
/photobarn Merchant Fax: (612) 555-9876 Merchant Name: Jewelry Warehouse (612) 555-8765 Merchant ID: 5 Merchant Address:
Merchant City: 24 Karat St. Merchant ZIP: NJ Merchant URL: info@jewelware.com Merchant Fax: (609) 555-6754 Merchant Name: Crazy John's Computers Merchant State: Platold Merchant E-mail: 00050 Merchant Phone: http://www.jewelware.com (609) 555-2212 Merchant ID: 6 Merchant Address:
Merchant City: 456 Enterprise St. Merchant ZIP: CO Merchant URL: crazy@merchandizing.net Merchant State: Wynnsville Merchant E-mail: 18888 Merchant Phone: http://www.merchandizing
.net/crazy Merchant Fax: (301) 555-9080 Merchant Name: Collector's Emporium (301) 555-8090 Merchant ID: 7 Merchant Address:
Merchant City: 256 Antiques Ave. Merchant ZIP: PA Merchant URL: n/a Merchant State: Oldenburg Merchant E-mail: 18999 Merchant Phone: n/a
(570) 555-1608 17
No comments:
Post a Comment