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

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:

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

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.

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

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

The existing queries appear, as shown in Illustration 3.

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

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

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

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.

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.

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.