MS Access for the Business Environment: Create a PivotTable View in Access - Page 3
July 1, 2003
Defining the Data Fields within the PivotTable View
Remaining within the Queries section of the Database window, let's take the following steps to begin construction of our new PivotTable view.
1. Click the new Customer_Orders_Query to select it
2. Click Open on the Database window toolbar, as partially shown in Illustration 12.
3. Select View --> PivotTable View from the main menu, as shown in Illustration 13.
After a few seconds, a blank PivotTable appears, with a floating PivotTable Field List appearing in the foreground, as shown in Illustration 14.
The blank PivotTable offers an excellent "conceptual" view of the basic makeup of a PivotTable. With a PivotTable we can create views of our data that strongly resemble, but far out power, a cross-tab query. The PivotTable allows us to define the data values that we wish to occupy multiple rows, columns, pages (or "layers") and summaries.
We can see above that, the center of the table contains numeric data ("measures"), while the rows and columns present (often hierarchical) dimensional data. We will see illustrations of how these areas of the conceptual "map" are populated in the steps that follow.
Let's define our view to effectively present the data set returned from our new query.
4. If the PivotTable Field List does not appear, initialize it clicking the Field List button, shown in Illustration 15, on the toolbar.
5. In the Field List, click Country to highlight it.
6. Select Filter Area in the selector box, to the right of the Add to button (at the bottom of the Field List), as shown in Illustration 16.
7. Click the Add to button.
8. In the Field List, click City to highlight it.
9. Select Column Area in the selector box, to the right of the Add to button.
10. Click the Add to button.
11. In the Field List, click Company Name to highlight it.
12. Select Column Area in the selector box, to the right of the Add to button.
13. Click the Add to button.
This adds the Company Name field to the right of the City field in the column section of the PivotTable.
14. In the Field List, click Order Date by Month to highlight it.
15. Select Row Area in the selector box, to the right of the Add to button.
16. Click the Add to button.
17. In the Field List, click Order ID to highlight it.
18. Select Detail Data in the selector box, to the right of the Add to button.
19. Click the Add to button.
20. Repeat steps 14 through 16 for the Product Name, Unit Price, Discount, and Quantity fields.
A small sample of the PivotTable view is partially shown in Illustration 17.
Now let's make our PivotTable more useful by adding calculated detail fields and totals.