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

Monday Dec 1st 2003 by William Pearson
Share:

Nest one query inside another, and hatch the power of subqueries. In this lesson, Bill Pearson leads a multi-step practice example in meeting a business need with a basic subquery in MS Access.

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. While the majority of the procedures I demonstrate will be undertaken with MS Access 2002, many of the concepts that we explore in the series will apply to other versions of MS Access.

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

While we have considered basic queries in past lessons, particularly as a basis for reporting and other topics, most of the queries we have encountered could have been assembled without writing "direct SQL." That is, they might have just as easily been constructed using the Design view, or perhaps even with the Query Builder, behind both of which MS Access constructs the SQL for us. There are, however, many cases where the SQL we need to handle real-world requirements cannot be assembled adequately with the Query Builder, or even within Design view. The MS Access documentation refers to several groups of these as "SQL-specific" queries, including pass-through, data definition, and union queries.

Subqueries are another group that requires special attention, and, while we can construct many of them within the query design grid, I find that situations requiring subqueries, as well as other sophisticated solutions, are best handled through the use of direct SQL. One of the best things about the SQL view in MS Access is that it allows us to "flip" from a query displayed / under construction in one of the two graphical modes to the actual SQL behind the scenes - and thus an excellent learning experience can be accessed by a simple click of the view button, which allows us to shift between Design, Datasheet, and SQL views within any query, and at any time, to suit our needs.

The objective of this lesson is to introduce subqueries within the context of MS Access, and to explore the creation and use of SQL subqueries to render a result dataset that meets an illustrative business need. To accomplish this, we will introduce the subquery, discussing its syntax in general as a part of overviewing its general use. Along with an introduction to the basic subquery, this lesson will include:

  • an examination of the syntax surrounding the use of the subquery;
  • an illustrative, hands-on example of the use of a subquery in a multi-step practice exercise;
  • a brief discussion of the results datasets that we obtain in each step of the practice example.

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

Introduction to Subqueries in MS Access

Subqueries provide us the capability of "embedding" a SELECT statement inside another SELECT statement. The result is that a query is filtered by the values in another query in the simplest form, but several subqueries can be nested into one SELECT statement. We generate queries to present only part of a set of data records, typically to perform analysis or to produce reports; a subquery allows us to filter that data even further.

We will examine the syntax of a basic subquery to gain an understanding of its components, and then we will build a subquery. Starting with a basic SELECT query, we will create a second SELECT query to meet an illustrative business need. The second query will "knit" the two queries together in a single, "consolidated" query. Our example will make it easier to understand how one query is modified by another to produce the results that information consumers require. We will accomplish this in multiple steps to illustrate the construction and use of the subquery in reaching our end objective.

Discussion

As we noted in the introduction, 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
  • DELETE

Nesting can also be within another subquery, and numerous levels of "subnesting" are possible. 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.

Syntax

Although subqueries can be used in many ways, some of the basic syntactical concepts can be laid out with the following common example:

SELECT OrderID, Freight FROM Orders 
WHERE Freight <
   (SELECT AVG(Freight) FROM Orders)
ORDER BY Freight DESC

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

Component

Description

Primary Query

SELECT OrderID, Freight FROM Orders

WHERE Freight < [VALUE]

ORDER BY Freight DESC

The primary query in the example is a simple SELECT statement whose output would be a list of Orders (by OrderID) whose Freight cost was less than a given VALUE. In our example, we substitute the VALUE with the subquery below.

We order by the Freight cost as a means of aligning our amounts for instant visual verification that the results delivered appear correct.

Subquery

SELECT AVG(Freight) FROM Orders

Another simple SELECT statement, whose output is a single value, the average Freight cost of all records in the Orders table.


Table 1: Subquery Syntax Components

With regard to the subquery component above,

SELECT AVG(Freight) FROM Orders 

$ 78.24 was returned from my copy of the Northwind database. The example query, containing the primary query and subquery, delivers the dataset partially shown in Illustration 1.


Illustration 1: Example Query Results (Partial View)

Queries such as the example, where "above - and - below average" considerations come into play, are a good application, in many cases, for subqueries. The potential uses for, and operations of, subqueries in general also begin to appear for many who, previously unacquainted with subqueries, examine a use such as this. I use subqueries in scenarios that range from fairly straightforward, as above, to quite sophisticated. We will revisit subqueries in future articles, after we establish some fundamental concepts, and explore some of these uses.

Let's move into a hands-on illustration to reinforce our understanding of subqueries, using the Northwind sample database.

Practice

We will reinforce our understanding of the basics by working through a multi-step practice example that illustrates various aspects of subquery syntax in operation. As we have in past lessons, we will create a simple query, then evolve it step by step 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 we have been approached by information consumers within the organization with a relatively straightforward business requirement: The consumers wish to have a simple list of suppliers from whom we purchase the products that we, in turn, sell to our customers, ranking them by our "total spend" on each. There are several ways to approach this in MS Access. A "ranking" scenario provides a good backdrop for illustrating the operation of subqueries, so we will take that route in providing the information to the consumers.

We will 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 in Access 2002.)

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 2.


Illustration 2: Inside Access, Northwind Main Switchboard

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

The existing queries appear, as shown in Illustration 3.


Illustration 3: Queries in the Northwind Database (Partial View - with New Button Circled)

NOTE: The queries that appear in your individual view may differ, obviously, depending upon past activities within the sample database, etc.

8.  Click the New button (shown circled in Illustration 3).

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 wish to use direct SQL, and to work with queries at a level that goes beyond working with wizards, or even the Design view we have used in the past.

11.  Select SQL View using the View Selector button in the main toolbar (it appears under the File item on the main menu), as shown in Illustration 6.


Illustration 6: Select SQL View

The SQL view editor appears, complete with a SELECT keyword in place, followed by the ubiquitous ending character for MS Access queries, the semicolon (";"). Illustration 7 depicts the initial view.


Illustration 7: Initial SQL View

Here we can enter, display, and / or modify a query using SQL directly, as we will throughout this lesson. We stated in our last lesson, Access Query Techniques: Crosstab Queries, that we can do many things here that might prove difficult or impossible in Design view, or within the realm of wizardry. And as I mentioned earlier, subqueries as a group require special attention, and, while we can construct many of them within the query design grid, I find that 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, so we will proceed with our practice example using direct SQL as a rule.

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

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

SELECT 
	ProductID, ProductName
FROM 
	Products
GROUP BY 
	ProductID, ProductName

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

13.  Save the query as ACC0701 (for "Step One.")

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

The resulting dataset appears as partially shown in Illustration 8.


Illustration 8: The SELECT Query Dataset

We have assembled the basic SELECT query, and run it to obtain the resulting dataset. 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 operation of the subquery in action, after having obtained a familiarity for the underlying data involved.

We note that the above query delivers a record set containing seventy-seven (77) products, which gives us a feel for the product population.

15.  Ensure that the query is saved as ACC0701.

Let's modify the query to bring in the corresponding supplier information for the products. We will do this via an inner join on the Supplier table, as shown in the next step.

16.  Shift back to SQL view, once again, if necessary.

17.  Add the following, immediately before ProductID on the line underneath the SELECT keyword of ACC0701:

CompanyName,

18.  Replace the following, underneath the FROM keyword:

Products

with the following:

Suppliers
      INNER JOIN Products 
         ON Suppliers.SupplierID = Products.SupplierID

19.  Add the following (be sure to include comma), immediately before ProductID on the line underneath the GROUP BY keywords:

CompanyName,

While all queries end in a semicolon (";") in MS Access, as we have noted in previous lessons, we need not fret its addition; MS Access will insert it automatically at run time.

The query should now resemble that shown in Illustration 9.


Illustration 9: The SELECT Query with Modifications

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

The resulting dataset appears as partially shown in Illustration 10.


Illustration 10: The Dataset Retrieved by the Modified SELECT Query (Partial View)

We note that, while the number of records returned remains the same as the number we saw retrieved in our last query (77), we now present the Supplier information that is associated with the various products.

21.  Save the query as ACC0702.

Our next step will be to add a total, via a SUM() function, of the Spend amount by supplier, by product. The business requirement did not specify any need to generate a total by product or even by supplier (although total supplier Spend might be deemed useful); the information consumers simply asked for a ranking of suppliers by amount of Spend for each. We will get there in a couple of steps, mainly to embellish our understanding of the data enroute to our final destination.

First, we'll modify the query to add the SUM() function. Our assumption will be, for purposes of this exercise, that Spend is computed from the Unit Price amount (in the Suppliers table) times total units (in our case, units on hand plus those we have formally ordered, and therefore obligated ourselves to purchase) of any given product.

22.  Shift back to SQL view, once again.

23.  Add a comma after the line underneath the SELECT keyword in query ACC0702, as shown:

CompanyName, ProductID, ProductName, 

24.  Add the following expression immediately after the new comma:

Sum((UnitPrice)*(UnitsInStock+UnitsOnOrder)) AS Spend

25.  Save the query as ACC0703.

The query should now resemble that shown in Illustration 11.


Illustration 11: The SELECT Query with Modifications

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

The resulting dataset appears as partially shown in Illustration 12.


Illustration 12: The Dataset Retrieved by the Modified SELECT Query (Partial View)

We note that, while the number of records returned remains the same number as we saw retrieved in our last query (77), we now present the supplier information that is associated with the various products. The total Spend value, based upon the query we have evolved, represents the total for each supplier-product combination. We can thus see that the dataset approaches our requirement, with the remaining steps to summarize total Spend by supplier, then to rank the suppliers, based upon the total Spend amount, to provide the basis for the simple ranking that the information consumers have requested.

27.  Shift back to SQL view, once again.

28.  Remove the following from the line immediately underneath the SELECT keyword in query ACC0703, as shown:

ProductID, ProductName,

To leave the following:


CompanyName,  
   Sum((UnitPrice)*(UnitsInStock+UnitsOnOrder)) AS Spend

29.  Remove the following from the line underneath the GROUP BY keyword in query ACC0703, as shown:

, ProductID, ProductName

To leave the following:

CompanyName  

30.  Save the query as ACC0704.

The query should now resemble that shown in Illustration 13.


Illustration 13: The SELECT Query with Modifications

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

The resulting dataset appears as partially shown in Illustration 14.


Illustration 14: The Dataset Retrieved by the Modified SELECT Query (Partial View)

We note that the number of records returned now matches the number of suppliers in the Suppliers table. A quick examination of the previous dataset, whereby the total Spend value represented the total for each supplier-product combination, reveals that our totals do, indeed, appear to be correct as a few sample additions will bear out. We are now ready to rank the vendors, to meet precisely the business requirement of our target audience.

32.  Click the New button atop the Database window.

The New Query dialog appears.

33.  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.

34.  Click Close on the Show Table dialog.

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

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

The SQL view editor appears.

36.  Type the following new query into the editor:


SELECT 
   CompanyName, Spend, 
   (SELECT COUNT(*) 
       FROM 
       ACC0704 AS CoSpendTotal
    WHERE 
       ACC0704.Spend <=  CoSpendTotal.Spend) 
	AS Rank
FROM 
   ACC0704
ORDER BY 
   Spend DESC

37.  Save the query as ACC0705.

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

The resulting dataset appears as partially shown in Illustration 15.


Illustration 15: The Query / Subquery Results Dataset

In this second query, another simple SELECT query, we have embedded our initial query (which we constructed in steps one through four above), as a subquery. This arrangement induces a scenario whereby the primary query executes the nested subquery for each row of the result set. The effect is that it performs a count of the rows whose Spend value exceeds or equals the Spend value of the current row, by performing a comparison between the original query and a clone of itself.

The row-by-row operation of the process might make our example inefficient within a "real world" realm involving a huge table, but our purpose here is simply to illustrate the operation of a subquery in reaching a reporting objective, and this example shows particularly well why such a construct lends itself to ranking operations.

39.  Ensuring that the query is saved as ACC0705, select File --> Close.

Under the auspices of the practice example we have explored, we can easily see the utility of subqueries in returning datasets to meet a business need. While the overhead involved in the subquery approach might render it suboptimal in some environments, and while various other considerations might come into play in deciding whether to deploy subquery functionality in our production environments, there are many, many scenarios where subqueries can be invaluable in helping us to meet our objectives. We will provide further illustrations of such instances, particularly where subqueries can be mingled with other techniques and functions to achieve desired ends, in prospective articles.

Conclusion ...

With this lesson we introduced subqueries within the context of MS Access, and explored their creation and use in rendering a result dataset to meet an illustrative business need. To accomplish this, we examined the syntax surrounding the use of an example subquery, and then began an illustrative, hands-on example of the use of a subquery in a multi-step practice exercise. We first built the initial SELECT query involved, to illustrate the underlying operation and its effects upon the data. After evolving the initial query, we created a second query, within which we nested the first, to demonstrate the operation of a subquery in our practice exercise. Finally, we briefly discussed various aspects of the results datasets that we obtained in each step of the practice examples.

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

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