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:
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
Click the icon
to start MS Access.
Access opens, and may display the initial dialog. If so, close it.
-> 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.)
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.
splash screen disappears, and is replaced by the Main Switchboard.
Click the Display
Database Window, or get there by an alternative approach.
arrive at the Database Window, which appears as depicted in Illustration
Illustration 2: Inside Access, Northwind Main Switchboard
under Objects in the Database window.
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.
Click the New
button (shown circled in Illustration 3).
Query dialog appears, as shown in Illustration 4.
Illustration 4: The New Query Dialog
the Design View option is selected, click OK.
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
on the Show Table dialog.
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.
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.