Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jul 1, 2003

MS Access for the Business Environment: Create a PivotTable View in Access - Page 8

By William Pearson

The PivotTable view now resembles that partially depicted (for the selected establishment and city in our example) in Illustration 34.

Click for larger image

Illustration 34: Partial PivotTable View with Our Modifications

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.

Illustration 35: Select Western U.S. Cities as a Column Filter

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.

Illustration 36: The Sort Ascending Button on the PivotTable Toolbar

31.  Select the Western U.S. field.

32.  Click the Collapse button on the toolbar.

The Collapse button is depicted in Illustration 37.

Illustration 37: The Collapse Button in the PivotTable Toolbar

The PivotTable view now appears in the compressed state shown in Illustration 38.

Illustration 38: The Collapsed PivotTable View

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.

» See All Articles by Columnist William E. Pearson, III

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM