We will again reinforce our understanding of the rudiments
by working through a multi-step practice example that illustrates the concepts
As we have in past lessons, we will create a simple query,
then evolve it to expose each 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 results datasets we obtain.
Let's say, for purposes of our exercise, that we have been
approached by organizational information consumers with a special year-end business
requirement: Accounting, at the direction of the external auditors, has asked
that we help them to provide information needed for a year-end, operational
cutoff test that the auditors would like to conduct. While the audit team has
the backup for the sales figures that the organization has generated for the
year 1997, they wish to verify the reports that they have for accuracy and
As a part of their test work, the audit team wishes to
review a sample list of the last independently recorded sales shipments for the
year, to ensure that they are properly reflected in the organization's
financial ledgers. To accomplish this, they want a report from the database
showing the last twenty orders of 1997 - by shipment date, (revenue is properly
recorded when earned, meaning that the shipping date is more relevant
than the order date.) They will use this sample list to verify that the sales
transactions to which the shipping records relate appear to be properly booked
in the financial system.
To summarize, we need to generate a list of the last twenty
shipments of 1997. This turns out to provide another excellent use of the TOP
keyword, as we will soon see. We will build the query through multiple steps,
so as to comment on its various components in a manner that reinforces the
concepts involved. First, we will create a core SELECT query, and then
we will add the refinements required to meet the information consumer's needs.
We will start MS
Access and proceed, taking the following steps:
Go to the Start button on the PC, and then navigate to the Microsoft
Access icon, as we have in previous lessons in this series.
Click the icon
to start MS Access.
Access opens, and may display the initial dialog. If so, close it.
-> 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 MS Access.)
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.
splash screen disappears, and is replaced by the Main Switchboard.
Click the Display
Database Window, or get there by an alternative approach.
arrive at the Database Window, which appears as depicted in Illustration
Illustration 4: Inside Access, Northwind Main Switchboard
under Objects in the Database window.
The existing queries
Click the New
button on the Database window toolbar.
Query dialog appears, as shown in Illustration 5.
Illustration 5: The New Query Dialog
the Design View option is selected, click OK.
Query dialog appears by default, with the Show Table dialog
appearing in front, as shown in Illustration 6.
Illustration 6: The Select Query and Show Table Dialogs
on the Show Table dialog.
Table dialog closes, leaving only the Select Query dialog present.