$34.98
Add to Cart
Inventory.accdb
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.
Answer will be sent by email as attachment.
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.
Answer will be sent by email as attachment.
