This lab assumes you are using MS Access 13 or above. You may use a higher version; however, be aware that there may be slight changes in the different versions. If your submission does not work in MS Access 13 or above, it is considered non-functioning and you will receive a zero.

Grading Rubric

TOTAL: 40 points

• Part A: 39 points
  • (3 pts) q01_all_items follows specifications
  • (3 pts) q02_AW_items follows specifications
  • (3 pts) q03_G_items follows specifications
  • (3 pts) q04_costly_items follows specifications
  • (3 pts) q05_midprice_items follows specifications
  • (3 pts) q06_cheap_items follows specifications
  • (3 pts) q07_SD_expensive_items follows specifications
  • (3 pts) q08_vendors_items follows specifications
  • (5 pts) Last Vendor-Item-Form follows specifications
  • (4 pts) Vendor-Item-Report follows specifications
  • (3 pts) q09_items_markup follows specifications
  • (3 pts) q10_item_averages follows specifications
  • (3 pts) q11_lowest5_items follows specifications
• Part B: 1 point
  • Follows requested project submission format

Guidelines and Policies

Getting Help

If you have a question, please post on Piazza, and we will answer as quickly as possible. We will try to answer questions and provide help within 24 hours of your request. If you do not receive a response in 24 hours, please send the request again.

Although we will answer questions, provide clarification, and give general help where possible up until the deadline, we will not help you with your submission within 24 hours of the deadline. We will not provide any help after the deadline.

Guidelines

This is an individual lab assignment. You must do the vast majority of the work on your own. It is permissible to consult with classmates to ask general questions about the assignment, to help discover and fix specific bugs, and to talk about high level approaches in general terms. It is not permissible to give or receive answers or solution details from fellow students.

You may research online for additional resources; however, you may not use material that was written specifically to solve the problem you have been given, and you may not have anyone else help you write the material or solve the problem. You may use resources found online, providing that they are appropriately and clearly cited, within your submission.

By submitting this assignment, you agree that you have followed the above guidelines regarding collaboration and research.

In this lab, you will learn to:

• Use and Design MS Access Queries

Querying a Database

Note: This is the second in a series of Database (Access) labs that build on each other. We will use a single database file and keep adding to that file as the labs progress. Please submit the version of the file as it exists after each lab is complete.

Problem: The manager of the Museum gift shop has come up with several questions she wants the database management system to answer, using the “Lab 6 Museum Gift Shop” database we created in Lab 6. Use queries created with Query Design to obtain answers to these questions.

General Instructions: If a query building instruction does not say “all fields”, then include ONLY the fields named in the instruction step but include them in the order listed in the instructions!

1. Open the “Lab6-lastname.accdb” database and File > Save As “Lab7-lastname.accdb”
2. Create a query for the Item table that includes all fields (in the same order as the table) and all records in the Items table. Close the design view and name the query: q01_all_items
3. Create a query for the Items table that includes the Item Number, Description, Wholesale Cost, and Vendor Code fields for all records where the vendor code is AW Save the query as: q02_AW_items
4. Create a query for the Items table that includes the Item Number, Description and Vendor Code fields for all items where the description starts with G. Save the query as: q03_G_items
5. Create a query for the Items table that includes the Item Number, Description, Wholesale Cost and Vendor Code for all items with a Wholesale Cost greater than $15.00. Save the query as: q04_costly_items
6. Create a query for the Items table that includes the Item Number, Description, Retail Price, and Vendor Code fields for all Items with a Retail Price between $5.00 and $14.99, INCLUDING those 2 values: q05_midprice_items
7. Create a query for the Item table that includes the Item Number, Description, On Hand, and Wholesale Cost fields for all Items where the number On Hand is less than 5 and the wholesale cost is less than $15 .00. Save the query as: q06_cheap_items
8. Create a query for the Items table that includes the Item Number, Description, Wholesale Cost, and Vendor Code for all Items that have a Wholesale Cost greater than $20.00 or a Vendor Code of SD. Save the query as: q07_SD_expensive_items
9. Create a query that joins the Vendors table and the Items table. Include the Vendor Code and Vendor Name from the Vendors table and the Item Number, Description, Wholesale Cost, and Retail Price fields from the Items table. Be sure your fields are in the Query Design grid in the specified order.
   
   Add a sorting rule to sort the query in ascending order by Description within Vendor Code. See the instructions in the Access Workbook under “To Sort on Multiple Keys”. Save the query as: q08_vendors_items
10. With the “q08_vendors_items” query open click the “Create” tab, then click the “Form” button (NOT the Form Design button). Use the scroll buttons at the bottom of the form to get to one of the items with YOUR Supplier code. Save the form, naming it: Vendor-Item-Form.
    1. You should now have TWO forms (one from lab 1-2).
11. In the Navigation Pane, right-click Vendor-Item-Form and rename it as Last Name Vendor-Item Form, where Last Name is your last name.
12. Use the report wizard to create a new report. The report uses the q08_vendors_items query.
    1. Use only the Vendor Name, Description, Wholesale Cost, and Retail Price fields from the query.
    2. Group by Vendor Name. Sorted ascending by Description within Vendor Name.
    3. Use the Tabular Stepped layout.
    4. Title it: Vendor-Item-Report
13. Create a query for the Items table that includes the Item Number, Description, Wholesale Cost, and Retail Price. Insert a calculated field Named Mark Up that computes the difference between Retail Price and Wholesale Cost (Retail Price - Wholesale Cost). Save the query as: q09_items_markup
14. Create a query for the Items table that displays the average Wholesale Cost and the average Retail Price of all Items. No other fields will appear. Save the query as: q10_item_averages
15. Create a query for the Items table that displays the Item Number, Description, On Hand, and Retail Price for the 5 Items with the lowest retail price. Save the query as: q11_lowest5_items

1. Modify the “q02_AW_items” query from instruction 3 so that you are prompted for the vendor code to display instead of coding “AW” as the vendor. Save this query as: q12_selectVendor_items
2. Save the workbook as Lab7-lastname.accdb

Submission

• Required code naming and organization:
  • Lab7-lastname.accdb

Every lab will have required submission guidelines. Please read submission requirements carefully. Any deviations from specifications on future labs or projects will result in significant point deductions or incomplete grades.