Database

Database

Question 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 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 • 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. Graded Project 5 FIGURE 4—Create a query on the Inventory table for repaired items. 6 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. 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
Powered by