Locate and Access the Data
Because we have created a preliminary
design, we have a complete idea of the data that the report will need to
access. In addition to our draft (shown in Illustration 1 above), we
might want to list the data fields that the report will contain. For purposes
of this exercise, we will note their identities as we build the query, from
which we will entrain them into the report.
Let's get started by opening MS Access,
and creating a query that will act as the source of data for the report.
Go to the Start button on the PC, and then navigate to the Microsoft
Click the icon
to start 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 Access 2002.)
The splash screen may appear, depending upon whether we have suppressed
splash screen disappears, and is replaced by the Main Switchboard, as we
have seen in earlier lessons.
Click the Display
Database Window, or get there by an alternative approach.
arrive at the Database Window.
under Objects in the Database
Click New on the Database
window toolbar, just above the Objects pane.
Query dialog appears, as shown in Illustration 2.
Illustration 2: 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 3.
Illustration 3: The Select Query Dialog (Compressed View),
with Show Table Dialog Foremost
following tables, highlighting each, and then clicking the Add button,
to add each successively to the Select Query dialog.
Click the Close
button on the Show Table dialog to close it.
Query dialog, upper portion, displays the newly added tables, appearing as shown
in Illustration 4.
Illustration 4: The Select Query Dialog, Selected Tables
notice that the joins / relationships have been placed automatically. This is
because the AutoJoin feature is enabled. (AutoJoin can be
disabled by going to Tools ->
selecting the Tables/Queries tab, and unchecking the Enable AutoJoin
review the joins for correctness, as always, and find them to be adequate. Next,
we select destination fields and, thus, determine the result datasets that the
query will generate.
each of the tables that follow, double-click the indicated fields to place it
in the corresponding field of the matrix in the bottom half of the Select
From the Customers
From the Products
From the Order
From the Order
Details table, select:
Query dialog displays the newly added tables and fields, appearing as
partially shown in Illustration 5.
Illustration 5: The Select Query Dialog, Selected Tables
and Fields (Partial View)