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

Tuesday Jul 1st 2003 by William Pearson
Share:

Explore the creation of a PivotTable view for a custom query. Join author Bill Pearson in a step-by-step procedure for using the new PivotTable functionality in MS Access 2000, to create and navigate a PivotTable view.

About the Series ...

This article continues the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. While the majority of the procedures I demonstrate will be undertaken with Access 2002, many of the concepts that we expose in the series will apply to numerous versions of MS Access.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: Create a Calculated Field with the Expression Builder.

Introduction to this Tutorial

This tutorial will introduce the PivotTable in MS Access. Among numerous enhancements that appear in Access 2002, one of the most exciting is the addition of PivotTable and PivotChart views for the tables and queries of the database. PivotTables and PivotCharts provide a means of interactive data analysis, thus broadening our business intelligence toolset in Access; both allow us to organize and summarize information into useful analysis presentations called views. We explore the PivotTable view in this lesson, and then expose the PivotChart view in Lesson 3: Create a PivotChart View in Access.

In this tutorial, we will focus upon the creation and use of PivotTable views in Access. This lesson will include:

  • Creation of a basic query upon which to base a PivotTable view.
  • A brief introduction to PivotTable Views, including navigation and features;
  • A practical walkthrough of the process of creating of a PivotTable view in an Access database.
  • An exploration of steps to take in modifying a PivotTable View
  • A brief discussion of formatting features.

Let's begin by discussing the features of the PivotTable view. Then, having an idea of the "end purpose," we will look at the steps required to create a PivotTable view to meet a hypothetical business need.

Introduction to the PivotTable

Access 2002 contains a PivotTable Wizard to create Excel PivotTables based upon

Access tables or queries. A PivotTable presents a cross-tabulation of our data;

We can define the data values for rows, columns, pages, and summarization. We will create an initial PivotTable based upon a query we build first, especially for the purpose of showing how the PivotTable is designed. The blank PivotTable that we see upon initializing the process will provide a good starting point to discuss further characteristics of PivotTables. The subsequent steps we take in building the view will provide hands-on opportunities to get a feel for how PivotTables work, in general.

Create a PivotTable View

To create a PivotTable view from the sample Northwind database that accompanies a typical installation of MS Access, we will take the following steps:

  • Select a query or table for presentation as a PivotTable;
  • Define the data fields within the PivotTable view;
  • Add calculated detail fields and calculated totals;
  • Format and organize the PivotTable view.

Each of these sections within our tutorial will provide ample practice in many of the options that are presented in designing a PivotTable.

Select a Query or Table for Presentation

In order to gain a bit of practice, while familiarizing ourselves with the data, we will first create a basic query, whose result set we will select as the basis for presentation via our PivotTable view.

We'll start Access and proceed, taking the following steps:

1.             Go to the Start button on the PC, and then navigate to the Microsoft Access icon, as we did in Lesson 1: Create a Calculated Field with the Expression Builder.

2.             Click the icon to start Access.

Access opens, and may display the initial dialog. If so, close it.

3.             Select File -> Open from the top menu, and navigate to the Northwind sample database (the file might also be accessed from the Open a File menu atop the task pane, if it has not been disabled previously, at the right side of the main window in Access 2002.)

4.             Select Northwind.mdb.

The splash screen may appear, as shown in Illustration 1.


Illustration 1: The Northwind Traders Splash Screen

NOTE: We can preclude the appearance of the splash screen each time we enter the sample Northwind database by checking the "Don't show this screen again." checkbox. For now, we will leave it unchecked.

5.             Click OK.

The splash screen disappears, and is replaced by the Main Switchboard, as shown in Illustration 2.


Illustration 2: Inside Access, Northwind Main Switchboard

6.             Click the Display Database Window, or get there by an alternative approach.

We arrive at the Database Window, which appears as depicted in Illustration 3.


Illustration 3: Inside Access, Northwind Main Switchboard

7.             Click Queries, under Objects in the Database window.

The existing queries appear, as shown in Illustration 4.


Illustration 4: Queries in the Northwind Database (New Button Circled)

NOTE: The queries that appear in your individual view may differ, depending upon past activities within the sample database, etc.

8.             Click the New button, shown circled in Illustration 4 above.

The New Query dialog appears, as shown in Illustration 5.


Illustration 5: The New Query Dialog

9.             Ensuring that the Design View option is selected, click OK.

The Select Query dialog appears by default, with the Show Table dialog appearing in front, as shown in Illustration 6.


Illustration 6: The Select Query Dialog, Show Table Dialog Foremost

10.         Select the following tables, highlighting each, and then clicking the Add button, to add each successively to the Select Query dialog.

  • Categories
  • Customers
  • Orders
  • Order Details
  • Products

11.         Click the Close button on the Show Table dialog to close it.

The Select Query dialog displays the newly added tables, appearing as shown in Illustration 7.


Illustration 7: The Select Query Dialog, Selected Tables (Compressed View)

We notice that the joins / relationships have been placed automatically. We review them for correctness, as always, and find them to be adequate. Next we select destination fields and, thus, determine the result datasets that the query will generate.

For each of the tables that follow, double-click the indicated fields to place it in the corresponding field of the matrix in the bottom half of the Select Query dialog.

12.         From the Customers table, select:

  • CompanyName
  • Region
  • City
  • Country

13.         From the Orders table, select:

  • OrderID
  • OrderDate
  • RequiredDate
  • ShippedDate

14.         From the Order Details table, select:

  • UnitPrice
  • Quantity
  • Discount

15.         From the Product table, select:

  • ProductName

16.         From the Categories table, select:

  • CategoryName
  • Description

The Select Query dialog displays the newly added tables and fields, appearing as partially shown in Illustration 8.


Illustration 8: The Select Query Dialog, Selected Tables and Fields (Partial View)

Now, let's run the query and examine the result set that it returns.

17.         Select Query --> Run from the main menu, as shown in Illustration 9.


Illustration 9: Select Query -> Run to Execute the Query

The query runs, and returns the data set, whose size is 2,155 rows, as partially displayed in Illustration 10.


Illustration 10: The Data Set Returned by Our New Query (Partial View)

18.         Select File --> Save As.

The Save As dialog appears.

19.         Type Customer_Orders_Query into the Save To field.

20.         Select Query from the dropdown selector for the As field.

The completed Save As dialog appears as shown in Illustration 11.


Illustration 11: The Completed Save As Dialog

We have now created and saved a query in our Access database. We will base our newly created PivotTable view upon this query, to demonstrate the steps involved.

21.         Click File --> Close to close the Customer_Orders_Query.

We are returned to the Database window.

As we have said before, a PivotTable view can be constructed for a table or a query. We created a custom query here, upon which to base a demonstration of the steps involved in PivotTable creation, while providing practice in query creation. We will thus understand a bit deeper the nature of the data that we will soon see in a different presentation.

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.


Illustration 12: The Database Window (Relevant Portions)

3.             Select View --> PivotTable View from the main menu, as shown in Illustration 13.


Illustration 13: Select View --> PivotTable View

After a few seconds, a blank PivotTable appears, with a floating PivotTable Field List appearing in the foreground, as shown in Illustration 14.


Illustration 14: A Blank PivotTable View Appears

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.


Illustration 15: The PivotTable Field List Button

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.


Illustration 16: The PivotTable Field List with our Selections

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.


Illustration 17: The PivotTable with Our Field Assignments (Sample View)

Now let's make our PivotTable more useful by adding calculated detail fields and totals.

Adding Calculated Detail Fields and Calculated Totals

While we can bring much of the data we need into the PivotTable simply by selecting the appropriate data fields, as we have seen above, we can also present derived detail data via calculated fields. We can also present calculated totals. These calculations can make our PivotTable far more useful to information consumers, as we will see by taking the following steps:

1.      Click the Calculated Totals and Fields button (shown in Illustration 18) on the PivotTable toolbar.


Illustration 18: The Calculated Totals and Fields Button

2.      Select Create Calculated Detail Field from the dropdown options that appear.

The PivotTable Field List appears, with a new field, whose default name is Calculated, appearing at its bottom, as shown in Illustration 19. In addition, the Properties dialog for the new Calculated field appears, typically in front of the PivotTable Field List, defaulted to show the Calculation tab.


Illustration 19: The PivotTable Field List, with New Field, and the Associated Properties Box

3.      On the Calculation tab, in the text area below the Name text box, type the following:

UnitPrice * Quantity - Discount

NOTE: We can use the Insert Reference To button, after selecting each of the fields in sequence (inserting the appropriate operators), as an option for inputting our calculation. This is perhaps cumbersome, but can often help us to ensure that the fields are represented correctly; an example here would be Unit Price, which appears in the PivotTable Field List as "Unit Price" (with a space between the words). If we simply type in "Unit Price," our calculation results in an error. However, if we build the calculation using the Insert Reference To button (a form of "expression builder" approach), the correct format of "UnitPrice" (no space between words) is input - saving us the time, ultimately, in clearing the error.

4.      Type "Net Sale" into the Name text box.

The Properties box, Calculation tab, appears as shown in Illustration 20.


Illustration 20: The Properties Box, Calculation Tab

 

5.      Click the Format tab on the Properties dialog.

6.      In the Number format selector, select Currency.

7.      Click the Bold button in the Text Format section of the Format tab.

The Properties box, Format tab, appears as shown in Illustration 21.


Illustration 21: The Properties Box, Format Tab

8.      Return to the Calculation tab.

9.      Click the Change button at the bottom of the Calculation tab.

10.  Close the Properties box and PivotTable Field List.

The new Net Sale calculated detail field appears within our PivotTable, a view of which is partially depicted in Illustration 22.

Click for larger image

Illustration 22: The Net Sale Calculated Field Appears (Partial View - Compressed)

11.  Click and drag the label of the Net Sale calculated field to the right of the Discount field.

12.  With the Net Sale field still selected, click AutoCalc on the PivotTable toolbar, and then click Sum.

The AutoCalc button, complete with its cascaded menu, is shown in Illustration 23.


Illustration 23: The AutoCalc Button with Cascaded Menu

13.  Click AutoCalc again, this time selecting Count to add another total field to our view.

When we use AutoCalc, we are creating calculated fields, just as we did with our "expression editor" approach for Net Sale. AutoCalc simply creates the fields with common aggregate functions that are built in, represented by the selections in the menu shown above.

Our PivotTable now appears as partially shown in Illustration 24.


Illustration 24: Partial PivotTable View, with Totals Fields

In addition to seeing our new calculated totals in the PivotTable view, we can see that they have been added to our PivotTable Field List, where we can easily access property setpoints for formatting and other settings.

14.  Click the PivotTable Field List button (see Illustration 15 for a view of the button) to resurrect the Field List.

The Field List appears, and displays our new calculated totals under Totals atop the list, as shown in Illustration 25.


Illustration 25: PivotTable Field List, Totals Expanded to Show New Calculated Totals

We have successfully created calculated detail fields and calculated totals. Now let's take a brief look at organizing and formatting our presentation a bit, to enhance its value to information consumers.

Formatting and Organizing the PivotTable View

Next we will examine ways we can organize the new PivotTable view to make it easy to use. We will format and organize our view, then briefly examine filtering, sorting and grouping.

1.      Click the new Sum of Net Sale calculated total in the PivotTable view to highlight it.

2.      Click the Properties button (shown in Illustration 26) on the PivotTable toolbar.


Illustration 26: The Properties Button on the PivotTable Toolbar

The Properties dialog appears for the Sum of Net Sale calculated total field.

3.      Click the Captions tab.

4.      Input Total Net Sale in the Caption text box.

The Properties dialog, Captions tab for the Total Net Sale calculated total appears as shown in Illustration 27.


Illustration 27: The Properties Dialog, Captions Tab, for the Total Net Sale Field

5.      Leaving the Properties dialog open, click the label for the new Count of Net Sale calculated total field on the PivotTable.

The Properties dialog switches to display the settings for the Count of Net Sale column.

6.      Input No. Line Items in the Caption text box.

The Properties dialog, Captions tab for the No. Line Items calculated total appears as shown in Illustration 28.


Illustration 28: The Properties Dialog, Captions Tab for the No. Line Items Field

7.      Close the Properties dialog.

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

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.

Conclusion...

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved