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.
1.
Go to the Start button on the PC, and then navigate to the Microsoft
Access icon.
2.
Click the icon
to start 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, depending upon whether we have suppressed
it before.
5.
Click OK,
as necessary.
The
splash screen disappears, and is replaced by the Main Switchboard, as we
have seen in earlier lessons.
6.
Click the Display
Database Window, or get there by an alternative approach.
We
arrive at the Database Window.
7.
Click Queries,
under Objects in the Database
window.
8.
Click New on the Database
window toolbar, just above the Objects pane.
The New
Query dialog appears, as shown in Illustration 2.
Illustration 2: 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 3.
Illustration 3: The Select Query Dialog (Compressed View),
with Show Table Dialog Foremost
10.
Select the
following tables, highlighting each, and then clicking the Add button,
to add each successively to the Select Query dialog.
-
Customers
-
Orders
-
Order Details
-
Products
11.
Click the Close
button on the Show Table dialog to close it.
The Select
Query dialog, upper portion, displays the newly added tables, appearing as shown
in Illustration 4.
Illustration 4: The Select Query Dialog, Selected Tables
(Compressed View)
We
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 ->
Options,
selecting the Tables/Queries tab, and unchecking the Enable AutoJoin
checkbox).
We
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.
For
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
Query dialog.
12.
From the Customers
table, select:
-
Customer ID
-
Company Name
-
City
-
Country
13.
From the Products
table, select:
14.
From the Order
table, select:
15.
From the Order
Details table, select:
-
Unit Price
-
Quantity
-
Discount
The 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)
» 
Comment and Contribute
