Case Problems

Download as a Word document: Case Problems Ch06 (32.0K)

You will practice retrieving, sorting and calculating data stored in several tables. The inventory is stored in a database named AC06CP01. Use select queries to answer the following questions regarding the inventory at Spinners Record Store, saving each query with an appropriate name. Be aware, that some of the queries will require joins where others may not:

• How many CDs does Eric Clapton play on? Which ones?
• What is the total value of all CDs (Hint: Calculate a new column called OnHandValue, Create a second query with a totals row, based on the first).
• Make a new query based on the previous step. Include the publisher field in the first column, Title in the second column, and group by the publisher, count the titles. Save this query with a different name (Choose: File, Save As)
• What is the Cost per Track of each CD? Which is the most expensive on a per track basis. Which is the least expensive

Save all queries, close all windows, including the database window. Then, exit Access.

R&B Home and Garden

In this exercise, you will prepare queries that will collect and calculate Invoice amounts. Use the data file named AC06CP02. Save all queries.

• The first query joins the Orders and Order_Line tables, an includes each field once. (Do not include the foreign key fields). For each item, calculate the total for that item, Ext Price:[NumberOrdered]*[QuotedPrice], format appropriately. Save the query as OrderDetail.
• Create an aggregate query to calculate the total of all invoices grouped by customer.
• Create a query called Invoice that joins the Customer table with the OrderDetails, Include all fields, one time each (Don't include the foreign key field(s)).
• Create queries to answer the following questions, save each query with an appropriate name:
• • List the description of all parts for which there are more than 100 units on hand and which are located in warehouse number 3.
  • List the description of all parts for which there are more than 100 units on hand or which are located in warehouse number 3.
  • List the number, name, and balance for each customer whose balance is between $100 and $500.
  • List the number, name and available credit for all customers.
  • How many parts are in item class HW?
  • What is the average balance for all customers?
  • What is the average balance for customers of each sales rep?
  • List each customer's number and name along with the number and name of the corresponding sales rep.

Save all queries. Close the data base window. Then, exit Access.

Case Problems Data Files Ch06 (37.0K)