Houston Community College Databases Project

Navigate to your Access Chapter 2 folder, and then double-click the downloaded file that displays your name—Student_2E_Biology Supplies and then enable the content.

Open both tables to examine the fields and data, and then Close both tables. Create a one-to-many relationship between your 2E Vendors table and your 2E Biology Lab Supplies table using the common field Vendor ID. Enforce Referential Integrity, and enable both cascade options. One vendor can supply many supplies. Create a Relationship Report with Normal Margins. Save it as 2E Relationships

Close All open objects. Open your 2E Vendors table. In the last record, in the Vendor ID field, select V-100, type V-001 and then press to save the record. Close the table.

Open your 2E Biology Lab Supplies table. Sort the records first in Descending order by Price Per Item and then in Ascending order by Category. Close the table, saving changes to the table.

Create a query in Query Design view using your 2E Biology Lab Supplies table to answer the question, What is the item ID, item name, room, location, and quantity in stock for all of the items, sorted in ascending order by the Room field and the Location field? Display the fields in the order listed in the question. Save the query as 2E Items by Room Query Close the query.

In the Navigation Pane, use your 2E Items by Room Query to create a new query object named 2E Item Categories Query and then redesign the query to answer the question, What is the item ID, item name, category, vendor ID, and quantity in stock for all items, sorted in ascending order by the Category field and the Vendor ID field? Display only the fields necessary to answer the question and in the order listed in the question. Close the query, saving the design changes.

In the Navigation Pane, use your 2E Items by Room Query to create a new query object named 2E Supplies Sort Query and then open the new query in Design view. Redesign the query to answer the question, What is the item name, category, price per item, and quantity in stock for all supplies, sorted in ascending order by the Category field and then in descending order by the Price Per Item field? Display only the fields necessary to answer the question and in the order listed in the question. Close the query, saving the design changes.

Using your 2E Supplies Sort Query, create a new query object named 2E Kits Query and then redesign the query to answer the question, What is item name, category, price per item, quantity in stock, and vendor ID for all items that have a category of kits, sorted in ascending order by the Item Name field? Do not display the Category field in the query results. Display the rest of the fields in the order listed in the question. Six records match the criteria. Close the query, saving the design changes.

Create a query in Query Design view using your 2E Vendors table to answer the question, What is the vendor ID, vendor name, and phone number where the phone number is blank, sorted in ascending order by the Vendor Name field? Display the fields in the order listed in the question. Two records match the criteria. Save the query as 2E Missing Phone Query Close the query.

Be sure all objects are closed. Open the Navigation Pane, be sure that all object names display fully, and then Close Access.