Featured Database Articles

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.

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.