MS Access for the Business Environment: Access Query Techniques: Subqueries, Part II - Page 3
February 2, 2004
Let's reinforce our understanding of the basics by working through a hands-on practice example, comprising multiple steps, that illustrates various aspects of subquery syntax in operation. As we have in past lessons, we will create a simple query, then evolve it by degrees, 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 a group of information consumers within the organization have come to us with a relatively routine (they say ...) business requirement: They need a list showing the dates of the last five sales in 1997 for each member of the corporate sales force. The consumers need the list to plan staffing and other logistics around what is expected to be a very busy holiday season, and as one of several sources of input to help management plan timing of issuance of future commission checks and other calendar events.
The consumers wish to have a simple list of employees, preferably with employee identification number and last name concatenated into a single field, together with a breakdown of the final five sales' order dates, for each respective sales person. We are cautioned, as well, that HR now dictates that employees be called "Colleagues," an eccentricity imposed as a policy of the new Vice President of Reporting Services, who has come to our small organization after being jettisoned (to "pursue other interests") from a long-held position in the pharmaceuticals industry.
While there are multiple ways to approach this in MS Access, this scenario provides an excellent opportunity to exploit a "top" scenario, similar to the one we explored with dates in our last lesson, within a subquery. This will be the approach we take in providing the needed information to the consumers.
Let's 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.)
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 3.
7. Click Queries, under Objects in the Database window.
The existing queries appear.
8. Click the New button atop the database window toolbar.
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 have in the last couple of lessons. This will allow us to use direct SQL, and to work with queries at a level that affords us maximum flexibility.
11. Select SQL View using the View Selector button in the main toolbar.
The SQL view editor appears, complete with a SELECT keyword in place, and ubiquitous ending character for MS Access queries, the semicolon (";"), in place.