DataWright Information Services

Consulting and Resources for Excel and Access

Using Subqueries -- an introduction

Returning grouped records in a single query

Sometimes you find that you can't get just the information you want using just a Totals query. For example, in a table with Manufacturer, Month, Sales and Year, you may want to view the highest sales by month and year, but also see which manufacturer had the highest sales. A sub query can allow you to include summarised information from the same or another table, without having to create another query first.

Let's run through a sequence of four queries to illustrate.

First query: All records

The first query has the SQL shown in Listing 1, and returns 1128 rows. In the sample database it is qryAll.

Listing 1: The starting query

SELECT Manufacturers.Manufacturer, UK_CarSales.Month, UK_CarSales.Sales, 
  UK_CarSales.Year
FROM UK_CarSales INNER JOIN Manufacturers 
ON UK_CarSales.ManufacturerID = Manufacturers.ManufacturerID;

Second query: Sales grouped by Month and Year

The second query has just 3 fields and returns 24 rows. The SQL is shown in Listing 2.

Listing 2: Grouped query, using 3 fields

SELECT UK_CarSales.Month, Max(UK_CarSales.Sales) 
  AS Max_Sales, UK_CarSales.Year
FROM UK_CarSales
GROUP BY UK_CarSales.Month, UK_CarSales.Year;

So far, so good. However, when you add the fourth field to this query you end up with 1128 rows again.

Listing 3: Grouped, but all rows displaying again

SELECT Manufacturers.Manufacturer, UK_CarSales.Month, Max(UK_CarSales.Sales)
  AS Max_Sales, UK_CarSales.Year
FROM UK_CarSales INNER JOIN Manufacturers
ON UK_CarSales.ManufacturerID = Manufacturers.ManufacturerID
GROUP BY Manufacturers.Manufacturer, UK_CarSales.Month, UK_CarSales.Year;

This is no good to us because the intention is to summarise the months, only listing the manufacturer with the highest sales for the month. One option is to use two queries: the first to generate the top manufacturer for the month, joined to a second query that completes the display. This doesn't always work -- sometimes the dynamics of the query change, and you find yourself trying to undo another unintended consequence. Fortunately, there is an option: the subquery.

The solution: Use a subquery

A subquery (or a subselect) is a way to tie the result set from one query to another. It gives you the flexibility to add descriptive fields such as Manufacturer without affecting the number of rows. There are some variants of this technique, with different strengths and weaknesses. In the first option (Listing 5), the criteria for the Sales field is another query that selects the maximum sales, filtered by month and year. Because UK_Sales is used both in the main query and the criteria, you need to create aliases for the table to distinguish them, or you will get an error when you try to run the query. First, let's look at the final display and then we'll create the subquery in steps.

You can create the components of a subquery using the query design grid, but you need to use SQL to finish the job.  

Creating the first part of the subquery

Create a new query and add UK_CarSales and Manufacturers to the query window. Join the tables on ManufacturerID.

Right-click UK_CarSales, select Properties, and change the Alias name to U. Close the Properties window. Repeat the process for Manufacturers, but alias the table to M.

Add the Manufacturer, Month, Sales and Year fields to the grid.

Creating the second part of the subquery

Create another new query and this time, add UK_CarSales to the query window twice.

Alias the first table as U and the second table as S, and close the Properties window.

Double-click Sales, Month and Year in table S to place the fields on the grid. Change the query to a Totals query by clicking the large ∑ on the toolbar, then change the summary function for Sales from Group By to Max. Change the summary functions for the other two fields to Where, and in the criteria row type [U].[Year] for the Year field, and [U].[Month] for the Month field. 

Combining the parts 

View > SQL View, then select all of the text and copy it. Switch back to the first query, and go into SQL view as well. You should see:

The SQL from the first part of the subquery

SELECT M.Manufacturer, U.Month, U.Sales, U.Year
FROM UK_CarSales AS U INNER JOIN Manufacturers AS M 
  ON U.ManufacturerID = M.ManufacturerID;

Remove the semi colon, press Enter to create a new line and type 

WHERE U.Sales)=(

Now paste the SQL that you copied from the second query. You will see this SQL:

Listing 4: The query requires some cleaning up 

SELECT M.Manufacturer, U.Month, U.Sales, U.Year
FROM UK_CarSales AS U INNER JOIN Manufacturers AS M 
  ON U.ManufacturerID = M.ManufacturerID
WHERE U.Sales=(SELECT Max(Sales) AS MaxOfSales 
  FROM UK_CarSales AS S, UK_CarSales AS U
  WHERE (([S].[Month] = [U].[Month])) AND (([S].[Year] = [U].[Year]))); 

This needs some cleaning up before it will work properly. The alias for Max(Sales) can be removed, and you need to delete the comma and UK_CarSales AS U in the second FROM statement (both sections are highlighted). The reason for this is that we want to filter the second query on the first, and removing the second alias makes that unambiguous.

Finally, remove some of the excess parentheses (Access gets a touch enthusiastic around WHERE clauses). You can also remove the square brackets around the table and field names, because there are no gaps in the names. The final result should look like this: 

Listing 5: A subquery using the criteria row of a field

SELECT U.Manufacturer, U.Month, U.Sales, U.Year
FROM UK_CarSales AS U
WHERE U.Sales=(SELECT Max(Sales)
FROM UK_CarSales AS S
  WHERE S.Month = U.Month AND S.Year = U.Year);

Run the query, and  you should get the result displayed previously.

Wrap-up

Often a standard query doesn't do the job. Either you need two or more queries in sequence, or you find that running sequential queries still gives unwanted results. When that happens, reach for a subquery. You will need to work with SQL to finish the job, but you will reduce the number of queries in your database and your reports will behave better.