MS Access for the Business Environment: Reporting in MS Access: Grouped Transactional Report Part I - Page 2
September 2, 2003
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.
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.
10. Select the following tables, highlighting each, and then clicking the Add button, to add each successively to the Select Query dialog.
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.
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:
13. From the Products table, select:
14. From the Order table, select:
15. From the Order Details table, select:
The Select Query dialog displays the newly added tables and fields, appearing as partially shown in Illustration 5.