Practice
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:
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.
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.
Illustration 3: Inside Access, Northwind Main Switchboard
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.
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 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.
» 
Comment and Contribute
