our previous lesson, we notice that the joins / relationships have been placed
automatically. We review them for correctness, as always, and find them to be
adequate. Next we select destination fields to determine the result dataset
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 Query dialog.
- From the Customers
From the Orders
From the Order
Details table, select:
From the Product
Query dialog displays the newly added tables and fields, appearing as
partially shown in Illustration 4.
Illustration 4: The Select Query Dialog, Selected Tables
let's run the query and examine the result set that it returns.
- Select Query--> Run from the main menu.
query runs, and returns the data set, whose size is 2,155 rows, as partially
displayed in Illustration 5.
Illustration 5: The Data Set Returned by Our New Query
Let's restrict our query to United States customers.
-> Design View from the main menu.
Query dialog reappears.
In the Customers
table, double-click the Country field.
field appears in the field's matrix, in the lower half of the Select Query
dialog, to the right of existing fields.
- Uncheck the Show
checkbox for the Country field.
- Type = USA into the Criteria box for
the Country field.
field appears as shown in Illustration 6.
Illustration 6: The Country Field. Set with Filter
--> Run from the main menu.
quickly verify that only U.S. Regions (that is, States, in this table of the
Northwind database) appear.
--> Save As.
As dialog appears.
Customer Orders into the Save To field.
from the dropdown selector for the As field.
completed Save As dialog appears as shown in Illustration 7.
Illustration 8: The Completed Save As Dialog
We have now created and saved the new query in our Access
database. We will base our PivotTable view, and thus our PivotChart view, upon
this query, to demonstrate the steps involved.
--> Close to close the General Customer Orders query.
returned to the Database window, where we can see our new query appears
among the queries list.
As we have said before, a PivotTable view can be constructed
for a table or a query. We created a custom query here, upon which to
base a PivotTable creation, which we will, in turn, use as the basis for a