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

Monday Feb 2nd 2004 by William Pearson
Share:

Use the TOP keyword within a subquery for more sophisticated results. Bill Pearson returns to his examination of subqueries with another practice example that meets an illustrative business need.

About the Series ...

This article continues the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. The majority of the procedures I demonstrate in this article and going forward will be undertaken within MS Access 2003, although most of the concepts that we explore in the series will apply to earlier versions of MS Access, as well.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: Create a Calculated Field with the Expression Builder.

Introduction to this Tutorial

In the lesson just before our last, Access Query Techniques: Subqueries, Part I, we introduced subqueries within the context of MS Access, and practiced their creation and use to help us to meet an illustrative business need. As a part of our exploration of subqueries, we examined the syntax surrounding the use of a straightforward sample subquery, and then began a hands-on example of the use of a subquery in a multi-step practice exercise. After evolving an initial query, we created a second query, within which we nested the first, to demonstrate the operation of a subquery in our practice exercise. We then discussed various aspects of the results datasets that we obtained in each step of the practice examples.

In our last article, Access Query Techniques: Using the TOP Keyword, we took a break from subqueries to examine the TOP keyword, both to illustrate its use in meeting another illustrative business need, and to prepare us for this lesson, where we will use the keyword in a continuing exploration of subqueries. We overviewed the TOP keyword, getting a taste of its operation in an undistracted scenario, and are now ready to proceed to the creation of a subquery that leverages TOP to illustrate more sophistication in the use of subqueries. We examined the syntax surrounding the use of TOP, together with its "modifier" keyword PERCENT, then performed practical examples of their uses to meet illustrative business needs. Finally, we discussed the results datasets that we obtained in each of the practice examples.

The objective of this lesson is to further delve into MS Access subqueries to render results that are more sophisticated for organizational information consumers. To accomplish this, we will introduce the use of the TOP keyword within another subquery scenario, in a series of steps that include:

  • an examination of the syntax surrounding the use of the subquery;
  • details of a "real world" need that can be met with the use of the TOP keyword in a subquery scenario;
  • an examination of the operation of the TOP keyword within the subquery;
  • a brief discussion of the results datasets that we obtain in each step of the practice example, and how they meet the business need objectives.

We will use the Northwind sample database that installs with MS Access for purposes of this lesson, in an attempt to make our discussion useful to virtually anyone who has access to the application.

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:

Component

Description

 

Primary Query

 SELECT CompanyName, ShippedDate
 FROM Customers INNER JOIN Orders
 ON Customers.CustomerID = Orders.CustomerID
 WHERE ShippedDate IN
   ([VALUE])
 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.

 

Subquery

 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.

Practice

Let's reinforce our understanding of the basics by working through a hands-on practice example, comprising multiple steps, that illustrates various aspects of subquery syntax in operation. As we have in past lessons, we will create a simple query, then evolve it by degrees, to expose several of the concepts that we have discussed. The query that we build will be our tool for constructing and executing the SQL we examine together, and for viewing the result datasets we obtain.

Let's say, for purposes of our exercise, that a group of information consumers within the organization have come to us with a relatively routine (they say ...) business requirement: They need a list showing the dates of the last five sales in 1997 for each member of the corporate sales force. The consumers need the list to plan staffing and other logistics around what is expected to be a very busy holiday season, and as one of several sources of input to help management plan timing of issuance of future commission checks and other calendar events.

The consumers wish to have a simple list of employees, preferably with employee identification number and last name concatenated into a single field, together with a breakdown of the final five sales' order dates, for each respective sales person. We are cautioned, as well, that HR now dictates that employees be called "Colleagues," an eccentricity imposed as a policy of the new Vice President of Reporting Services, who has come to our small organization after being jettisoned (to "pursue other interests") from a long-held position in the pharmaceuticals industry.

While there are multiple ways to approach this in MS Access, this scenario provides an excellent opportunity to exploit a "top" scenario, similar to the one we explored with dates in our last lesson, within a subquery. This will be the approach we take in providing the needed information to the consumers.

Let's start MS Access and proceed, taking the following steps:

1.  Go to the Start button on the PC, and then navigate to the Microsoft Access icon, as we did in Lesson 1: Create a Calculated Field with the Expression Builder.

2.  Click the icon to start MS Access.

MS Access opens, and may display the initial dialog. If so, close it.

3.  Select File -> Open from the top menu, and navigate to the Northwind sample database (the file might also be accessed from the Open a File menu atop the task pane, if it has not been disabled previously, at the right side of the main window.)

4.  Select Northwind.mdb.

The splash screen may appear.

NOTE: As we have noted earlier in the series, we can preclude the appearance of the splash screen each time we enter the sample Northwind database by checking the "Don't show this screen again." checkbox. For now, we will leave it unchecked.

5.  Click OK.

The splash screen disappears, and is replaced by the Main Switchboard.

6.  Click the Display Database Window, or get there by an alternative approach.

We arrive at the Database Window, which appears as depicted in Illustration 3.


Illustration 3: Inside Access, Northwind Main Switchboard

7.  Click Queries, under Objects in the Database window.

The existing queries appear.

8.  Click the New button atop the database window toolbar.

The New Query dialog appears, as shown in Illustration 4.


Illustration 4: The New Query Dialog

9.  Ensuring that the Design View option is selected, click OK.

The Select Query dialog appears by default, with the Show Table dialog appearing in front, as shown in Illustration 5.


Illustration 5: The Select Query and Show Table Dialogs

10.  Click Close on the Show Table dialog.

The Show Table dialog closes, leaving only the Select Query dialog present.

We will now move to SQL view, as we have in the last couple of lessons. This will allow us to use direct SQL, and to work with queries at a level that affords us maximum flexibility.

11.  Select SQL View using the View Selector button in the main toolbar.

The SQL view editor appears, complete with a SELECT keyword in place, and ubiquitous ending character for MS Access queries, the semicolon (";"), in place.

As I mentioned in Part I, subqueries as a group require special attention, and, while we can craft many of them within the query design grid, situations requiring subqueries are best handled through the use of direct SQL. This is also the best environment from which to gain a basic understanding of SQL, as many of us become aware with our first challenges to meet real business needs with MS Access. Our practice efforts in this lesson, like the previous two lessons, will be undertaken using direct SQL throughout.

We will begin the steps of the exercise by composing a simple SELECT query, and then we will gain an understanding of the dataset that it returns; our query will focus solely on the Orders table in its first step.

12.  Type the following basic SELECT query into the editor:


SELECT
 
TOP 5 OrderDate

FROM 

Orders   

ORDER BY

OrderDate DESC

We can run the query at this stage by selecting Query --> Run from the main menu.

13.  Save the query as ACC09-01.

14.  Select Query --> Run from the main menu.

The resulting dataset appears as partially shown in Illustration 6.


Illustration 6: The Initial SELECT Query Dataset

Before going further, let's investigate an apparent issue that appears. We have asked for the "top" five (or five most recent in the table) order dates, but we see eight records in the result dataset. As we illustrated in our last lesson, Using the TOP Keyword, the reason for what appears to be a deviation from the expected results is simply a manifestation of the standard behavior of the TOP keyword in the event of a "tie." When equal values are present in the range from which we are selecting the "top" records, all records that have the equal value are returned. In the immediate case, we asked for the five most recent Order Dates in the table, sorted in descending order. Starting from the "top" of this list, the "top four" records contain the same date, 06-May-1998. When we move to the next date, the "fifth from the top," we see a new date, 05-May-1998. However, 05-May-1998 occurs for the next four records, until, at the ninth record, the Order Date becomes 05-May-1998. Because TOP treats "ties" as equal, all records containing 05-May-1998 (a total of four) are returned. This results in the eight records that we see in the result dataset.

To prove this concept, we can open the Orders table, sort the rows by Order Date descending (by highlighting the column heading for Order Date and clicking the downward arrow button on the toolbar), then examine the "top" rows in the newly sorted table. The resulting Orders table view appears as partially shown in Illustration 7, where the "tied" rows are indicated by the bracket I have added in the illustration.


Illustration 7: The "Top" Dates that "Tie for Fifth Place" in the Orders Table (Partial View)

We have assembled the basic SELECT query, and run it to obtain the results dataset, examining our results for accuracy. Our objective in doing so is to begin setup of the first of two independent SELECT queries. The second query will "enclose" the first as a subquery, as we shall see. In this way, we can witness the subquery in action, after having obtained a familiarity for the underlying data involved.

We noted that the above query delivers a record set containing eight dates, for reasons we have explained. However, a review of the requirements of the information consumers indicates an adjustment to our query is in order: We need to limit the latest five dates selected to the year 1997.

15.  Ensure that the query is saved as ACC09-01.

16.  Shift back to SQL view, once again.

17.  Add the following, immediately before the ORDER BY clause of ACC0901:


WHERE 
Orders.OrderDate < #01-Jan-1998#

18.  Save the query as ACC09-02.

The query should now resemble that shown in Illustration 8.


Illustration 8: The Initial SELECT Query with Modifications

19.  Select Query --> Run from the main menu.

The resulting dataset appears as shown in Illustration 9.


Illustration 9: The Dataset Retrieved by the Modified SELECT Query

We can easily verify accuracy and completeness of the returned dataset through a quick look at the Orders table, as before, or via an independent query. This time, the dates played into our TOP statement well, with no "ties" in visually inconvenient places.

Our next step will be to build an independent SELECT query, which will be destined to become the primary query that houses the SELECT query with the TOP statement as we left it at our last step. We will first create this SELECT query to give us a layout to meet precisely the business requirements of the information consumers that requested it; we will then tweak it for any refinements we deem appropriate.

20.  Close query ACC09-02.

We are returned to the view of existing queries.

21.  Click the New button atop the Database window

The New Query dialog appears.

22.  Ensuring that the Design View option is selected, click OK.

The Select Query dialog, with the Show Table dialog appearing in front, appears as before.

23.  Click Close on the Show Table dialog.

The Show Table dialog closes, leaving only the Select Query dialog present.

24.  Select SQL View using the View Selector button once again.

The SQL view editor appears.

25.  Type the following new query into the editor:


SELECT 

CStr(Employees.EmployeeID)+"  -  "+LastName AS Colleague, 

OrderDate

FROM 

Employees INNER JOIN Orders 

ON 

Employees.EmployeeID = Orders.EmployeeID

ORDER BY 

Employees.EmployeeID ASC, Orders.OrderDate DESC

26.  Save the query as ACC09-03.

27.  Select Query --> Run from the main menu.

The resulting dataset appears as partially shown in Illustration 10.


Illustration 10: The Primary Query Results Dataset Initial (Partial View)

While we appear to have constructed a query that generates the layout required to meet the consumers' needs, we obviously have returned the complete population of customer orders by Colleague and Order Date, there being no filtering whatsoever in place at this stage.

Next, we will begin the marriage of the two SELECT queries, embedding the query with the TOP keyword we constructed first into the primary query we have constructed and saved as ACC09-03.

28.  Save the query again as ACC09-04, to prevent any accidental damage to ACC09-03.

29.  Select SQL View using the View Selector button once again.

The SQL view editor appears.

30.  Type the following new query into the editor:[lbc]

31.  Select SQL View using the View Selector button once again.

The SQL view editor appears.

32.  Type the following into the editor, between the


ON 

Employees.EmployeeID = Orders.EmployeeID

clause and the


ORDER BY 

Employees.EmployeeID ASC, Orders.OrderDate DESC

clause:


WHERE 

((Orders.OrderDate) 

IN 
(SELECT
 
	TOP 5 OrderDate

FROM 

	Orders   

   
WHERE 


	Orders.EmployeeID = Employees.EmployeeID 


	AND Orders.OrderDate < #31-Dec-1997#


ORDER BY

	OrderDate DESC))

The query should now resemble that shown in Illustration 11.


Illustration 11: The Modified Query, Incorporating Subquery

33.  Save the query once again as ACC09-04.

34.  Select Query --> Run from the main menu.

The resulting dataset appears as partially shown in Illustration 12.


Illustration 12: The Query / Subquery Results Dataset (Partial View)

In this, our second SELECT query, we have embedded our initial query (which we constructed in steps 12 through 17 above), as a subquery. This arrangement induces a scenario whereby the primary query executes the nested subquery, applying it as a filter against the primary result dataset. The presence of the subquery within the IN clause means that the dataset returned from the subquery is compared to the dataset from the primary SELECT statement to determine the dataset that is returned. Our addition of the join in the WHERE clause, which joins a field from the nested subquery with a field within the primary query, allows our construct to return the appropriate, differing results for each of the employees returned by the primary query.

As we discussed in Part I, the mechanics of the process that underlies our final query might make a construct similar to our example inefficient, within a "real world" scenario involving a huge table. Our purpose here is simply to illustrate the operation of a subquery in reaching a reporting objective. In this case, the subquery incorporates the TOP keyword as a part of its operation, to give us the "top" data relating to the members of a much larger population. While we used TOP in our example to narrow a result dataset to the latest five dates' data for the employee set returned by the primary SELECT query, we might just as well have used TOP to select a specified top (or bottom) number of occurrences based upon another value.

35.  Ensuring that the query is saved as ACC09-04, select File --> Close.

Within the practice examples we have explored in Parts I and II of this lesson, we can get a good idea of the utility of subqueries in returning datasets to meet business needs. While various considerations (such as the overhead involved in the operation of a subquery construct) come into play in deciding whether to deploy subquery functionality in our production environments, there are many scenarios where they can be invaluable in helping us to support information consumers in our organizations.

Conclusion ...

With this lesson, we continued the examination of MS Access subqueries that we began with Part I, extending our exploration of their construction and use to meet additional business needs. We examined the syntax surrounding the use of an example subquery, reviewed the expected results from the same example and then began an illustrative, hands-on example of the use of a subquery in a multi-step, practice exercise.

We began with the construction of a relatively basic SELECT query, to illustrate the underlying operation and its effects upon the data. After evolving the initial query, we created a second SELECT query, within which we then nested the initial query, to demonstrate the operation of a subquery, containing the TOP keyword, in our practice exercise. Finally, we briefly discussed various aspects of the results datasets that we obtained at relevant points throughout the practice example.

» See All Articles by Columnist William E. Pearson, III

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved