Chapter 13 – Advanced Queries
INTRODUCTION
Let us continue our Query adventure by introducing:
- Ability to create a query with multiple tables of source data
- Perform aggregate functions in a query to tabulate data
Guided Exercises
Go to your IU OneDrive and open your username-HomeGoodsStore database.
The Relationships diagram is at the end of the instructions if you would like to view it on paper. It can be opened from the Database Tools tab in your Access file, too. When reading the directions for a query, you need to decide which table(s) to use and looking at the Relationships can be helpful.
-
Did you remember the Totals Row? You have been given the task of contacting customers from Indiana and Illinois whose feedback has indicated that a follow-up from the store is needed. Create a query that list customers’ FirstName, LastName, PhoneNumber, and EmailAddress as well as the Category from which they purchased the product for which they left feedback. Only list customers who are marked as needing followed up with, and make sure each customer appears only once for each category of item that they purchased. Sort the results in ascending order by LastName. Save the query as qryGE5.
- Create a query that calculates the order total for each order that has been shipped. Display the ReceiptID and a calculated field called OrderTotal which is based on the price and quantity of the items ordered. Sort the results in descending order by OrderTotal. Format the calculated field as Currency. Save the query as qryGE6.
- Create a query that shows the total revenue for all orders that have been shipped. Your results should show one field called TotalRevenue that has just one record, the value of the total revenue. Format this field as Currency. Save the query as qryGE7.
- Create a query that shows each category, how many products are in each category, and the total number of items sold in each category during the second quarter of 2012. Display the Category field and the two calculated fields. The first calculated field should be named NumProducts and should display how many products are in each category; the second calculated field should be called TotalItemsSold and should calculate the total number of items sold in each category during the second quarter. The results should only include orders that were shipped since items in cancelled orders were not actually sold. Sort the results in descending order by TotalItemsSold. Save the query as qryGE8.
Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.
More Practice
Work you should do before our next class
Go to your IU OneDrive and open your username-HomeGoodsStore database.
- The manager wants to contact every employee who has had a cancelled order to find out why the order was cancelled. Create a query that lists these employees’ FirstName, LastName, PhoneNumber, and Type. Limit the type to Office and Cell phone numbers. Make sure each employee only appears once in the list for each type of contact and that you are only including employees who have had cancelled orders. Sort the records in ascending order by LastName. Save the query as qryMP5.
- Create a query that displays full-time employees who are enrolled in the medical benefits plan at this company but are not enrolled in the dental benefits plan. The query should display the employees’ FirstName, LastName, and Department. Sort the results in ascending order by LastName, then in ascending order by FirstName. Save the query as qryMP6.
- Create a query that summarizes the purchases for each region. Your query should display the RegionName field as well as two calculated fields. The first calculated field should be named NumItemsSold and should display how many total items were sold in each region. The second calculated field should be named RegionRevenue and should display the total revenue brought into the store by the customers in each region. Format the RegionRevenue field as Currency and sort in descending order on this field. The results should only include orders that were shipped. Save the query as qryMP7.
- Create a query to summarize how much each customer spent in the Bedroom and Kids Room departments. List the customers’ FirstName, LastName, and TotalSpent. The results should only be for orders that were shipped since orders that were cancelled do not actually bring in revenue. Sort the results in Ascending order by LastName and format the calculated field as Currency. Save this query as qryMP8.
- The employees had a contest during the holiday season to see who could bring in the most revenue during November and December of 2012. Create a query that shows each employee’s FirstName and LastName as well as a calculated field called HolidaySales that finds how much total revenue each employee brought into the company during those two months. Only shipped orders should be included. Sort the results in descending order by HolidaySales and format that field as Currency. Save the results as qryMP9.
Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.
