As in
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.
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.
- From the Customers
table, select:
-
From the Orders
table, select:
-
From the Order
Details table, select:
-
UnitPrice
-
Quantity
-
Discount
-
From the Product
table, select:
The Select
Query dialog displays the newly added tables and fields, appearing as
partially shown in Illustration 4.
Illustration 4: The Select Query Dialog, Selected Tables
and Fields
Now,
let's run the query and examine the result set that it returns.
- Select Query--> Run from the main menu.
The
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
(Partial View)
Let's restrict our query to United States customers.
-
Select View
-> Design View from the main menu.
The Select
Query dialog reappears.
-
In the Customers
table, double-click the Country field.
The Country
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.
The Country
field appears as shown in Illustration 6.
Illustration 6: The Country Field. Set with Filter
-
Select Query
--> Run from the main menu.
We can
quickly verify that only U.S. Regions (that is, States, in this table of the
Northwind database) appear.
-
Select File
--> Save As.
The Save
As dialog appears.
-
Type General
Customer Orders into the Save To field.
-
Select Query
from the dropdown selector for the As field.
The
completed Save As dialog appears as shown in Illustration 7.
Illustration 8: The Completed Save As Dialog
-
Click OK.
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.
-
Click File
--> Close to close the General Customer Orders query.
We are
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
PivotChart view.
» 
Comment and Contribute
