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

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

News Via 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 7

By William Pearson

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

Illustration 29: Partial PivotTable View, with New Captions Displayed

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.

Illustration 30: The Hide Details Button on the PivotTable Toolbar

The total fields now display alone in the PivotTable view, as partially shown in Illustration 31.

Illustration 31: Partial PivotTable View, Details Hidden

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.

Illustration 32: Select USA as the Country Page Filter

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.

Illustration 33: The Show Details Button on the PivotTable Toolbar

MS Access Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



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