Case Problems

Download as a Word document: Case Problems Ch07 (28.0K)

The objective of this exercise is to give you practice manipulating the records stored in a database through the creation and execution of queries. Open the data file titled "AC07CP01". Some of the records in the catalog are only purchased for special orders, are no longer stocked, and need to be archived. You will move these records to a new table, and then delete them from the original location. To identify the records, the On-hand value is equal to 0 (zero). Perform the following steps to complete the exercise:

• Create a select query to select the records for movement. Save the query as qrySpecialOrders. Two records should be selected with a Unit on hand value of 0. Be sure to include all fields.
• Once you have verified the correct records are being selected, change the query to a make table query, save it as qrySpecialOrders-MakeTable, the table name should be tblSpecialOrders.
• After running the above query, verify the results (a new table, with two records) convert it to a delete query, and save the query as qrySpecialOrders-Delete. Run the query and close the window.
• Since you may want to move records again, open the make table query, change it to an append query and save it as qrySpecialOrders-Append. Do not run the query.
• The CD "Now and Zen" and "Magic Touch" have sold out. Update the records manually (no query). It has been decided to move these items to the special orders table. Run the append query, and then the delete query to move the records to the special orders table and remove them from the CDs table. Verify your results by opening the datasheets of the tables.

When finished, close all windows and then exit Access.

R&B Home and Garden

In this exercise you will develop a custom sort order, parameter queries, and use some of the special query wizards to analyze orders and inventory for R&B Home and Garden Center. Open the data file titled "AC07CP02".

• Your job is to create a custom sort order to the tblPart table. Instead of sort alphabetically, you will sort so that house wares (HW) are listed first, followed by appliances (AP), then sporting goods (SG). Hint: use the switch function to assign numeric values for the sort. Save the query as qryCustomSort.
• The next query you create is a parameter query for finding items in a specific warehouse. Create a query based on the tblPart table. Specify a parameter to find records of items stored in one of the three warehouses. Save the query as qryWarehouseParameter.
• There are some new customers without sales reps. Use the wizard to create a find unmatched records to find the customers without matching sales reps. Make a note of the customer numbers, to verify your results in a later step. Save the query as qryCustomersWithoutReps.
• The company recently hired a new sales rep; enter the new record into the tblSales_Rep table. ID# 01, Janet Jones, 345 Washington, Lansing, MI 49224, 5% commission rate.
• Create an update query that assigns all unassigned accounts to Janet. There should be three accounts. Run as a select query, before changing to an update. Save the query as qryRepUpdate. Verify the results by reviewing the customer table. Then printing the customer table, if you have a printer attached.

Close all windows, and then exit Access.

Case Problems Data Files Ch07 (67.0K)