Microsoft Access 2010 Chapter 3 Lab 2 Maintaining the Walburg Energy Alternatives Database
Problem: The management of the Walburg Energy Alternatives recently acquired some items from a store that is going out of business. You now need to append these new items to the current item table. You also need to change the database structure and add some validation rules to the database.
Use the database modified in the In the Lab 2 of Chapter 2 on page AC 134 for this assignment. You also will use the More Items database from the Data Files for Students. See the inside back cover of this book for instructions for downloading the Data Files for Students, or see your instructor for information on accessing the required files.
Perform the following tasks:
1. Open the More Items database from the Data Files for Students.
2. Create a new query for the Item table and add all fields to the query.
3. Using an append query, append all records in the More Items database to the Item table in the Walburg Energy Alternatives database, as shown in Figure 3 – 87.
4. Save the append query as Walburg Append Query and close the More Items database.
5. Open the Walburg Energy Alternatives database and then open the Item table in Datasheet view. There should be 20 records in the table.
6. The items added from the More Items database do not have a vendor assigned to them. Assign items 1234 and 2234 to vendor JM. Assign item 2216 to vendor AS. Assign items 2310 and 2789 to vendor SD.
7. Create an advanced filter for the Item table. The filter should display records with fewer than 10 items on hand and be sorted in ascending order by Description. Save the filter settings as a query and name the filter Reorder Filter.
8. Make the following changes to the Item table:
a. Change the field size for the On Hand field to Integer. The Format should be fixed and the decimal places should be 0.
b. Make Description a required field.
c. Specify that the number on hand must be between 0 and 50. Include validation text.
d. Add a calculated field Inventory Value (On Hand*Cost) following the Cost field. Format the field as currency.
9. Save the changes to the table design. If a dialog box appears indicating that some data may be lost, click the Yes button.
10. Add the Inventory Value field to the Inventory Status Report. Place the field after the Cost field. Save the changes to the report.
11. Specify referential integrity between the Vendor table (the one table) and the Item table (the many table). Cascade the update but not the delete.
12. Submit the revised More Items database and the Walburg Energy Alternatives database in the format specified by your instructor.