Using Subqueries — an introduction
Download (207,489 bytes)
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.
