Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Feb 2, 2004

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

By William Pearson

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.


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.


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:




Primary Query

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

The primary query in the example is a relatively simple SELECT statement whose output would be a list of Customers (by CompanyName) to whom our company shipped merchandise within a given data-range VALUE.

In our example, we substitute the VALUE with the subquery below.



 SELECT TOP 5 ShippedDate
     FROM Orders
    ORDER BY ShippedDate DESC

Another relatively simple SELECT statement, whose output is a range of dates, the last five shipping dates for each customer (where applicable).

Table 1: Subquery Syntax Components

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.

Illustration 1: Example Results - Subquery Component

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.

Illustration 2: Example Results - Combined Query Components (Partial View)

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.

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM