MS Access for the Business Environment: Create a PivotTable View in Access - Page 7
July 1, 2003
The PivotTable view appears similar to that partially shown in Illustration 29. Note that the calculated totals appear both as columns and as rows.
Click for larger image
8. Select the Total Net Sale field.
9. Click the Hide Details button (shown in Illustration 30) on the toolbar to hide the detail fields.
The total fields now display alone in the PivotTable view, as partially shown in Illustration 31.
10. Click the black arrow next to the Country field in the Filter area of the PivotTable.
11. De-select All by clicking the checkmark next to it.
12. Select USA as the Filter selection by checking the box at its left on the menu.
Our USA selection appears in the cascading menu, as depicted in Illustration 32.
13. Click OK.
The PivotTable adjusts to reflect USA cities across the column headings. The Country field in the filter area also now displays USA underneath a blue arrow.
Now let's group a set of Cities upon which we might want to focus for a specific business need.
14. With the SHIFT key depressed, click the Portland, San Francisco and Seattle column headings.
The SHIFT key allows us to select multiple fields that are in adjacent ranges. The CTRL key allows us to select multiple non-adjacent fields.
15. Right-click one of the selection, and click Group Items from the context menu that appears.
The result is the creation of a custom group, whose default name is City1.
16. Right-click City1 in the PivotTable view.
17. Select Properties from the context menu that appears.
18. Replace City1 in the Caption box with Focus Cities.
19. Click Group1 in the PivotTable view.
The existing Properties box changes to reflect the properties of Group1.
20. Replace Group1 in the Caption box with Western U.S..
21. Scroll right on the PivotTable, at the same level as the new Western U.S. group, and select the Other group that appears.
The existing Properties box changes to reflect the properties of Other.
22. Replace Other in the Caption box with General U.S.
23. Select the label of the Let's Stop N Shop field, just below the San Francisco City field.
24. Click the Show Details button (see Illustration 33) on the toolbar.