MS Access for the Business Environment: Access Query Techniques: Subqueries, Part II - Page 2

February 2, 2004

A Return to Subqueries in MS Access

As we learned in Access Query Techniques: Subqueries, Part I, subqueries provide us the capability of "embedding" a SELECT statement inside another SELECT statement. In this way, a query can be filtered by the values in a query that is nested within it, in the simplest form. As we stated in Part I, the whole reason we generate a query is to return a subset of a population of data records to which we have access, usually to produce reports, perform analysis and so forth. As we noted, a subquery allows us to filter a returned dataset even further.

Much as we did in Part I, we will build a subquery in several steps, noting the purposes of each part as we progress through the construction. This will give us an understanding of the components, beginning with a basic SELECT query, then continuing to the creation of a second SELECT query, to meet an illustrative business need. The first query in our example, which will make use of the TOP keyword, will be nested within the second, "primary" query, which will "knit" the two queries together into a single, "consolidated" query. Our example will illustrate how one query is modified (e.g. restricted) by another to produce the results that information consumers require.

Discussion

As we noted in Part I, a subquery is formed when we nest an SQL SELECT statement inside another SQL statement. Nesting can be within another SELECT statement, or within other types of SQL statements, including INSERT INTO, SELECT INTO, UPDATE and DELETE. Recall that nesting can also be within another subquery, with numerous levels of "subnesting" possible.

We have seen that a subquery typically acts as a substitute for an expression, within a WHERE or HAVING clause, where the SELECT statement of the subquery generates a value set that is evaluated by the clause, or within the field list of a SELECT statement. In this lesson, we will see how the TOP keyword can be used as a component of a subquery to help us meet the business needs of the information consumers that we support.

Syntax

Let's review briefly the syntactical concepts, describing them as we have in earlier lessons, in an example that looks ahead to our practice exercise.

SELECT CompanyName, ShippedDate
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE ShippedDate IN
   (SELECT TOP 5 ShippedDate
     FROM Orders
     WHERE Orders.CustomerID = Customers.CustomerID
     ORDER BY ShippedDate DESC)
ORDER BY CompanyName ASC, ShippedDate DESC

The example SQL above contains the components described in Table 1:

 SELECT CompanyName, ShippedDate
 FROM Customers INNER JOIN Orders
 ON Customers.CustomerID = Orders.CustomerID
 WHERE ShippedDate IN
   ([VALUE])
 ORDER BY CompanyName ASC, ShippedDate DESC

 SELECT TOP 5 ShippedDate
     FROM Orders
    ORDER BY ShippedDate DESC

With regard to the Subquery component above,

SELECT TOP 5 ShippedDate
    FROM Orders
    ORDER BY ShippedDate DESC

a set of dates was returned from my copy of the Northwind database, as shown in Illustration 1.

We note that, similar to the TOP datasets returned for dates in our last lesson, the last five shipment dates in the database are returned, because we ordered the shipment dates in a descending (DESC) fashion (meaning "latest first," within a series of dates). While this does, in essence, what we ask it in standalone mode, when we insert the subquery component into the primary query, we add a WHERE clause to the subquery to contain a join with a field, Customer ID, in the primary component. The result is that the last five shipments for each individual customer are reflected, as opposed to the shipments that occurred for each customer over the same set of five dates (which would likely be less useful information).

When the two components are combined, with the foregoing concepts taken into consideration, the query generates the dataset partially depicted in Illustration 2.

Information sets similar to those returned by the example, where our objective is to present a "top (activity or other measure) by customer" listing of details - details that vary by customer, as to what, specifically, the "top" values will be - often lend themselves to subqueries. As we discussed in Part I, subqueries can be useful in scenarios that range from fairly straightforward, as above, to quite sophisticated. It is reasonable to expect that we will revisit subqueries numerous times in our series, over the months to come.

Let's move into a hands-on illustration to reinforce our understanding of the use of the TOP keyword in a subquery scenario, once again using the Northwind sample database.