MIS - TWO ACCESS ASSIGNMENT PROJECT

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 the volunteers using Microsoft Access:  Create a series of queries to answer questions associated with the BoarDome Database.

Instructions:  Perform the following tasks after you open the database you created for Assignment One:

 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.

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.

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.

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.

 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.

 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.

 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.

 Restrict the records retrieved in Task 7 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.

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

 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).

 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.

SSave the changes.

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

MN04              board game     4          $17.50             $21.94             WW