MS Access for the Business Environment: Access Query Techniques: Using the TOP Keyword - Page 3

Practice

We will again reinforce our understanding of the rudiments by working through a multi-step practice example that illustrates the concepts in operation.

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 completeness.

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:

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 MS Access.)

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.

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

The existing queries appear.

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

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

The Show Table dialog closes, leaving only the Select Query dialog present.