2 assignments for Microsoft Access

MIS 204

Access Assignment #2

(25 Points)

Note: Access Assignment #1 must be completed first before beginning Assignment #2.

Problem/Objectives of the Assignment:

The employees who staff the gift shop have determined a number of questions they want the database management system to answer. You must obtain answers to the questions posed by creating a series of queries to answer questions associated with the Northern Lakes Brewery Database.

Instructions: Perform the following tasks. (SAVE all queries and include your last name on each query when you save it. Ex. ProductId-Noce)

1. Create a query for the Product Id, Description and Selling Price for all the records in the Product Table sorted by Product Id. Label ProductId Query.

2. Create Only a query for the Product Id, Description and Selling Price for all products with the Vendor Code of MS. Label as MS Query.

3. Create a query for the Product Id and Description for all products where the Description begins with the letters, "Wi". Label as Wi Query.

4. Create a query for the Product Id, Description, and Vendor Code for all products that cost more than $20. List the records in descending order by Cost. Label as Cost Query.

5. Create a query for the Product Id and Description for all products that have a Selling Price of $5 or less. Label as Selling Price Query.

6. Create a query for all fields for those products that cost more than $10 and where the number of units on hand is less than 10. Label as Low Inventory Query.

7. Create a query for all fields for those products that have a Vendor Code of WW or have a Selling Price less than $10. Label as WW-Vendor Code Query.

8. Join the Product Table and the Vendor Table. Create a query for the Product Id, Description, Cost, Name and Telephone Number fields. Label the query as Product and Vendor Query.

9. Restrict the records retrieved in Task 8 to only those products where the number of units on hand is less than 10 (Also show the Number of Units on Hand). Label the query as Inventory Less than 10 Query.

10. Select Product Id, Description, On Hand and Cost. Compute the On Hand Value (On hand*cost) for all records in the table. Label as On Hand Query. HINT: (Enter your formula in the blank field to the right of Cost).

11. Create a query for the Average Cost and Average Selling Price for all products. (Just two fields should be displayed). Label as Average Query.

12. Create and save the following validation rules for the Product Table in Design view.

a. Make the Description field a required field.

b. Ensure that any lowercase letters entered in the Product Id field are converted to uppercase.

c. Specify that the on hand units must be between 0 and 100. Include validation text.

13. Save the changes.

14. Open the Product Form you created in Assignment #1, and add the following record to the Product table.

MN04 beer board game 4 $17.50 $21.94 WW

Output Requirements: Submit the database to the Canvas dropbox.