Microsoft Access 2010 Chapter 2 Lab 2: Querying the Walburg Energy Alternatives Database

Description

Microsoft Access 2010 Chapter 2 Lab 2: Querying the Walburg Energy Alternatives Database

Problem: The manager of the Walburg Energy Alternatives store has determined a number of questions he wants the database management system to answer. You must obtain answers to the questions posed by the manager.

Instructions: Use the database created in the In the Lab 2 of Chapter 1 on page AC 67 for this assignment, or see your instructor for information on accessing the fi les required for this book.

Perform the following tasks:
1. Open the Walburg Energy Alternatives database and create a query that includes all fields and all records in the Item table. There should be only one column in the design grid. Name the query Lab 2-2 Step 1 Query.
2. Create a query that includes the Item Number, Description, Cost, and Vendor Code fields for all items where the vendor code is JM. Save the query as Lab 2-2 Step 2 Query.
3. Create a query that includes the Item Number and Description fields for all items where the description starts with the letters, En. Save the query as Lab 2-2 Step 3 Query.
4. Create a query that includes the Item Number and Description fields for all items with a cost less than $4.00. Save the query as Lab 2-2 Step 4 Query.
5. Create a query that includes the Item Number and Description fields for all items with a selling price greater than $20.00. Save the query as Lab 2-2 Step 5 Query.
6. Create a query that includes all fields for all items with a vendor code of AS and where the number on hand is fewer than 10. Save the query as Lab 2-2 Step 6 Query.
7. Create a query that includes all fields for all items that have a selling price greater than $10.00 or a vendor code of JM. Save the query as Lab 2-2 Step 7 Query.
8. Join the Vendor table and the Item table. Include the Vendor Code and Vendor Name fields from the Vendor table and the Item Number, Description, On Hand, and Cost fields from the Item table. Sort the records in ascending order by item number within vendor code. Save the query as Vendor-Item Query.
9. Create the form shown in Figure 2 – 93. The form uses the Vendor-Item Query.
10. Create a query that includes the Item Number, Description, On Hand, and Cost fields. Calculate the inventory value (on hand*cost) for all records in the table. Assign the alias Inventory Value to the calculated field. Change the caption for the On Hand column to In Stock. Format the Inventory Value field as currency with two decimal places. Sort the records in descending order by inventory value. Save the query as Lab 2-2 Step 10 Query.
11. Create a query that calculates and displays the average cost of all items. Save the query as Lab 2-2 Step 11 Query.
12. Create a query that calculates and displays the average cost of items grouped by vendor code. Save the query as Lab 2-2 Step 12 Query.
13. Submit the revised database in the format specified by your instructor.