MS Access for the Business Environment: Access Query Techniques: Crosstab Queries - Page 2

November 3, 2003


We will confirm our understanding of the basics we have covered so far, by using the TRANSFORM statement in a multi-step practice example that illustrates various aspects of its 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. As we shall also see in prospective lessons within this series, the query will be our tool for constructing and executing the SQL we examine together, and for viewing the result datasets we obtain.

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, as shown in Illustration 1.

Illustration 1: Inside MS Access, Northwind 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.

Illustration 2: Inside Access, Northwind Main Switchboard

7.  Click Queries, under Objects in the Database window.

The existing queries appear, as shown in Illustration 3.

Illustration 3: Queries in the Northwind Database (Partial View - with New Button Circled)

NOTE: The queries that appear in your individual view may differ, depending upon past activities within the sample database, etc.

8.  Click the New button, shown circled in Illustration 3 above.

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.