MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1 - Page 3
December 1, 2003
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.
7. Click Queries, under Objects in the Database window.
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.
8. Click the New button (shown circled in Illustration 3).
The New Query dialog appears, as shown in Illustration 4.
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.
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.
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.