MS Access for the Business Environment: Create a PivotTable View in Access - Page 8
July 1, 2003
The PivotTable view now resembles that partially depicted (for the selected establishment and city in our example) in Illustration 34.
Click for larger image
25. Click the black arrow next to the Focus Cities field in the column area of the PivotTable.
The selection menu appears, with the All box checked.
26. De-select All by clicking the checkmark next to it.
27. Select Western U.S. by checking the box at its left on the menu, as shown in Illustration 35.
28. Click OK.
The selection menu closes and our PivotTable becomes filtered to the Western U.S. group of Cities.
29. Select the Unit Price field label in the Let's Stop N Shop details area.
30. Click the Sort Ascending button (see Illustration 36) on the toolbar.
31. Select the Western U.S. field.
32. Click the Collapse button on the toolbar.
The Collapse button is depicted in Illustration 37.
The PivotTable view now appears in the compressed state shown in Illustration 38.
There are many other actions we could take to make analysis easier and perhaps more effective, depending upon the needs of the ultimate targeted audience for our PivotTable. It is easy to see that the addition of the PivotTable to Access 2002 will be a welcome enhancement among business analysts everywhere.
With this lesson we introduced the PivotTable in MS Access 2002, and explored some of the ways that we can use it for interactive data analysis. In showing how we can use the PivotTable to broaden our Business Intelligence capabilities in Access, we organized and summarized information within a query result set that we created and executed for that specific purpose. After a brief introduction to PivotTable views, we performed a hands-on creation of a PivotTable view of our query, then modified and formatted various parts to expose presentation options that the PivotTable offers.