MS Access for the Business Environment: Create a PivotChart View in Access - Page 3

August 4, 2003

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.

  1. From the Customers table, select:
    • CompanyName
    • City
    • Region
  2. From the Orders table, select:
    • OrderID
    • OrderDate
  3. From the Order Details table, select:
    • UnitPrice
    • Quantity
    • Discount
  4. From the Product table, select:
    • ProductID
    • ProductName

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.

  1. 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.

  1. Select View -> Design View from the main menu.

The Select Query dialog reappears.

  1. 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.

  1. Uncheck the Show checkbox for the Country field.
  2. 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

  1. 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.

  1. Select File --> Save As.

The Save As dialog appears.

  1. Type General Customer Orders into the Save To field.
  2. 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

  1. 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.

  1. 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.