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

Monday Aug 4th 2003 by William Pearson
Share:

Build a PivotChart view from a PivotTable, constructed from a custom query. Join author Bill Pearson in a hands-on exploration of the new PivotChart functionality in MS Access 2002.

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 PivotChart in MS Access. As we discovered in our last lesson, Create a PivotTable View in Access, of several enhancements that appear in Access 2002, exciting new presentation functionality is offered by 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.

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

  • Creation of a basic query upon which to base a PivotTable view;
  • Creation of a PivotTable view in an Access database;
  • A brief introduction to PivotChart views, including various features;
  • Creation of a PivotChart view, based upon the PivotTable view we have created for this purpose;
  • An examination of the navigation and modification of a PivotChart View;
  • A brief discussion of formatting features.

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

Introduction to the PivotChart

Access 2002 allows us to easily shift from an existing PivotTable view to a PivotChart view. While the PivotTable and PivotChart views have much in common with regard to layout structure, the PivotChart view focuses on summaries / totals, while the PivotTable view concerns itself more with presenting data details. The PivotChart's focus on summary information is largely due to its graphic nature.

Another difference lies in the areas that the two presentations display: The PivotChart substitutes series (a group of related data fields) and categories (most often composed of a single data point representing each series) for the row and column areas, respectively, found in the PivotTable view. A legend typically presents various colors that map to each series; categories typically manifest themselves in the chart as x-axis labels.

With regard to the relationship between a PivotTable view and a PivotChart view, we need to keep in mind that any changes we make in the PivotTable layout will affect the PivotChart view, and vice versa. This relationship exists in contrast to the relationship between the PivotTable / PivotChart view and other views in which we might cast the underlying forms, queries and tables, whose layouts are completely independent of those presented in the PivotTable / PivotChart view.

As we noted in our last lesson, Access 2002 makes the creation of a PivotTable easy with a PivotTable Wizard. In this article, we will create a PivotTable based upon a simple query, and then base our PivotChart upon that PivotTable. We will create an initial PivotTable based upon a query we build first; the subsequent steps we take in creating a PivotChart view will provide hands-on opportunities to get a feel for how PivotCharts work.

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 for presentation as a PivotTable;
  • Define the data fields within the PivotTable view;
  • Add calculated detail fields and calculated totals.

Each of these sections within our tutorial will provide practice in designing a PivotTable, while preparing for the primary objective of creating a PivotChart view. For more information on PivotTable views, please refer to our last lesson, Create a PivotTable View in Access.

Select a Query for Presentation

Our first step in getting to a PivotTable view will be, as we discovered in our last lesson, the creation of a basic query; the result set of this query will serve as the basis for presentation via our PivotTable view, then via our PivotChart view, as a dependent result.

Let's get started, taking the following steps:

  1. Go to the Start button on the PC, and then navigate to the Microsoft Access icon.
  2. Click the icon to start Access.

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

  1. 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.)
  2. Select Northwind.mdb.

    The splash screen may appear; if so, close it by clicking OK.

The Main Switchboard appears.

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

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


Illustration 1: Inside Access, Northwind Main Switchboard

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

The existing queries appear.

  1. Click the New button atop the window.

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


Illustration 2: The New Query Dialog

  1. 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.

  1. Select the following tables, highlighting each, and then clicking the Add button, to add each successively to the Select Query dialog.
    • Customers
    • Orders
    • Order Details
    • Products
  2. 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 3.


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

As in our previous lesson, 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 to determine the result dataset 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.

  1. From the Customers table, select:
    • CompanyName
    • City
    • Region
  2. From the Orders table, select:
    • OrderID
    • OrderDate
  3. From the Order Details table, select:
    • UnitPrice
    • Quantity
    • Discount
  4. From the Product table, select:
    • ProductID
    • ProductName

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


Illustration 4: The Select Query Dialog, Selected Tables and Fields

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

  1. Select Query--> Run from the main menu.

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


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

Let's restrict our query to United States customers.

  1. Select View -> Design View from the main menu.

The Select Query dialog reappears.

  1. In the Customers table, double-click the Country field.

The Country field appears in the field's matrix, in the lower half of the Select Query dialog, to the right of existing fields.

  1. Uncheck the Show checkbox for the Country field.
  2. Type = USA into the Criteria box for the Country field.

The Country field appears as shown in Illustration 6.


Illustration 6: The Country Field. Set with Filter

  1. Select Query --> Run from the main menu.

We can quickly verify that only U.S. Regions (that is, States, in this table of the Northwind database) appear.

  1. Select File --> Save As.

The Save As dialog appears.

  1. Type General Customer Orders into the Save To field.
  2. Select Query from the dropdown selector for the As field.

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


Illustration 8: The Completed Save As Dialog

  1. Click OK.

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

  1. Click File --> Close to close the General Customer Orders query.

We are returned to the Database window, where we can see our new query appears among the queries list.

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 PivotTable creation, which we will, in turn, use as the basis for a PivotChart view.

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 General Customer Orders query to select it
  2. Click Open on the Database window toolbar to run and display the query.
  3. Select View --> PivotTable View from the main menu.

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


Illustration 8: A Blank PivotTable View Appears

We discovered in our previous lesson how the PivotTable allows us to define the data values that we wish to occupy multiple rows, columns, pages and summaries. Let's define our view to effectively present the data set returned from our new query, in preparation for transforming that presentation to the more graphic PivotChart view.

NOTE: If the PivotTable Field List does not appear, initialize it clicking the Field List button, shown in Illustration 9, on the toolbar.


Illustration 9: The PivotTable Field List Button

  1. In the Field List, click Region to highlight it.
  2. 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 10.


Illustration 10: The PivotTable Field List with our Selection

  1. Click the Add to button.

We see the Region field appear in the Filter Area of the PivotTable (the upper left hand corner).

  1. In the Field List, click City to highlight it.
  2. Select Column Area in the selector box, to the right of the Add to button.
  3. Click the Add to button.
  4. In the Field List, click Company Name to highlight it.
  5. Select Column Area, again, in the selector box, to the right of the Add to button.
  6. Click the Add to button.

This adds the Company Name field to the right of the City field in the column area of the PivotTable.

  1. In the Field List, click Order Date to highlight it.
  2. Select Row Area in the selector box, to the right of the Add to button.
  3. Click the Add to button.
  4. In the Field List, click Order ID to highlight it.
  5. Select Detail Data in the selector box, to the right of the Add to button.
  6. Click the Add to button.
  7. Repeat steps 14 through 16 for the following fields, in the order shown:
    • Product Name
    • Quantity
    • Unit Price
    • Discount.

A small sample of the PivotTable view is partially shown in Illustration 11.


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

  1. Close the Field List, as desired.

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

Adding Calculated Detail Fields and Calculated Totals

Let's round out our data presentation with the addition of derived detail data via calculated fields and calculated totals. These calculations can make our PivotTable, and ultimately our PivotChart, 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 12) on the PivotTable toolbar.


Illustration 12: The Calculated Totals and Fields Button

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

The PivotTable Field List appears (unless already present), with a new field, whose default name is Calculated, appearing at its bottom. 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.

  1. On the Calculation tab, in the text area below the Name text box, type the following:
		Quantity*UnitPrice-((Quantity*UnitPrice)*Discount)

NOTE: For comments and recommendations on the use of the Insert Reference To button, see our last lesson, Create a PivotTable View in Access.

  1. Type Total Net Sale into the Name text box.

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


Illustration 13: The Properties Box, Calculation Tab

  1. Click the Format tab on the Properties dialog.
  2. In the Number format selector, select Currency.
  3. Click the Bold button in the Text Format section of the Format tab.

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


Illustration 14: The Properties Box, Format Tab

  1. Return to the Calculation tab.
  2. Click the Change button at the bottom of the Calculation tab.
  3. Close the Properties box and PivotTable Field List.

The new Total Net Sale calculated detail field appears within our PivotTable.

  1. Click and drag the label of the Total Net Sale calculated field to the right of the Discount field, if necessary.
  2. With the Total 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 15.


Illustration 15: The AutoCalc Button with Cascaded Menu

NOTE: For comments on the use of the AutoCalc button, see our last lesson, Create a PivotTable View in Access.

Our PivotTable now appears as partially shown in Illustration 16 .


Illustration 16: Partial PivotTable View, with Totals Fields

We have successfully created a PivotTable view with calculated detail fields and calculated totals. Now let's build a PivotChart view based upon our PivotTable data, and organize and format our data presentation within the PivotChart to enhance its value to information consumers.

Create a PivotChart View

Microsoft Access 2002 provides a robust new technique, the PivotChart view, for online analysis. In addition, PivotCharts provide an excellent option for including charts in our forms and reports: We can embed a form that is open in PivotChart view as a subform in another form or report. Numerous advantages exist in using PivotChart views over chart controls.

As we discussed in the introduction, numerous differences exist between PivotTable and PivotChart views. Let's review the steps together for converting our existing PivotTable view to a PivotChart view, beginning where we left off with the PivotTable view in the last section.

  1. Select View -> PivotChart View from the main menu.

The PivotChart view appears in its raw form, as shown in Illustration 17.

Click for larger image

Illustration 17: PivotChart View, before Refinements

We will remove some of the extraneous fields to demonstrate the potential of refined views in using the PivotChart. Keep in mind that any field we remove from the PivotChart remains available for selection in the same chart; while the removed item no longer appears in the chart, all that is required to resurrect the field is to call upon the Field List, which resembles the Field List we have used in the PivotTable view above and in our previous lesson.

  1. Select Company Name at the right of the PivotChart (called the Series area).
  2. Press the DELETE key.
  3. Select Order Date at the bottom of the chart (called the Categories area).
  4. Press the DELETE key.

We note that, although we have deleted the Company Name and Order Date fields, from the Series and Categories areas respectively, Company Name and Order Date continue to appear in the Field List as a selection option.

  1. Click-select the Order Date by Month field in the Field List, as depicted in Illustration 18.


Illustration 18: Select Order Date by Month in the Field List

  1. Drag the Order Date by Month field to the Category fields area (where a box labeled "Drop Category Fields Here" is currently displayed), and drop within the boxed area.

The PivotChart assumes the attributes depicted in Illustration 19.


Illustration 19: Select the Modified PivotTable View

  1. Click the Years label (down arrow) that has appeared in the Category area of the chart.
  2. Uncheck Year 1996 to remove it from the chart.
  3. Expand Year 1997 by clicking the "+" sign to its immediate left.
  4. Deselect Quarters 3 & 4 in 1997.

From an accounting perspective, this makes the two years comparable, as Year 1998 only has two quarters recorded.

  1. Click OK.

The PivotChart appears as shown in Illustration 20.


Illustration 20: Quarters 1 & 2, 1997 and 1998

The PivotChart offers options for manipulating the data and enhancing the appearance of the graph, changing the chart type, modifying the axis lines and labels, and assorted other standard chart attributes. We will further explore these in the next section, where we will organize and format the view we have created.

Formatting and Organizing the PivotChart 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 we will change the chart type; finally, we will examine general formatting and organization of the view.

  1. Right-click the chart we have created, in any blank area.

A context menu appears, as shown in Illustration 21.


Illustration 21: The Context Menu

  1. Click the Chart Type option that appears in the context menu.

The PivotChart Properties dialog appears, defaulted to the Type tab, as shown in Illustration 22.


Illustration 22: The Chart Properties Dialog, Type Tab

We note that the current chart is set to exhibit the Clustered Column style, according to the description at the bottom of the Type tab. We also note that we have numerous other choices, not only within the Columnar types (the options appear in the form of pictures in the right pane of the Type tab), but among other chart types that are different entirely (listed in the left pane of the Type tab).

  1. Select the 3D Column Clustered type (the second option in the middle row of columnar types).

The 3D Column Clustered type selection appears in Illustration 23.


Illustration 23: Select the 3D Column Clustered Type

Our PivotChart appears, after our Type change, as shown in Illustration 24.


Illustration 24: PivotChart Reflects 3D Column Clustered Type

NOTE: The exact appearance of results may vary, based upon the scale parameters that result from "compression" of the PivotChart display. The images that appear, for example, in my illustrations are often "squeezed" to accommodate the space available in the document.

Now let's perform a few formatting and organization steps.

  1. Right-click the Axis Title label at the bottom of the chart.
  2. Select Properties from the context menu that appears.

The Properties dialog appears, defaulted to the General tab, as shown in Illustration 25.

Click for larger image

Illustration 25: Properties Dialog - General Tab for Bottom Axis Title

  1. Click the Format tab.
  2. Type Q1 / Q2 Results into the Caption box at bottom.
  3. Click the Bold button atop the dialog.

The Format tab - Properties dialog appears as depicted in Illustration 26.


Illustration 26: Properties Dialog - Format Tab for Bottom Axis Title

  1. Leaving the Properties dialog open, click the Axis Title label at the left of the chart.
  2. Type Total Net Sale into the Caption box at the bottom of the Format tab.
  3. Click the Bold button atop the dialog.

The Format tab - Properties dialog appears as depicted in Illustration 27.


Illustration 27: Properties Dialog - Format Tab for Left Axis Title

  1. Close the Properties dialog.
  2. Right-click the PivotChart in any blank area.
  3. Select Properties from the context menu that appears.

The Properties dialog appears, defaulted to the General tab.

  1. Click the Add Legend button, shown circled in Illustration 28.


Illustration 28: Add Legend Button, Chart Properties Dialog - General Tab

  1. Close the Chart Properties dialog.

Our PivotChart appears, after our enhancements, as shown in Illustration 29.


Illustration 29: The PivotChart Reflects Our Enhancements

  1. Right-click the Plot area (the gray area that contains the data columns - also known as "markers" - in our PivotChart).
  2. Select Properties from the context menu that appears.
  3. Select the Border / Fill tab, as necessary.
  4. In the Fill Color section of the tab, click the Color button to reveal the color selection palette.
  5. Select the Lavender swatch from the palette, as shown circled in Illustration 30.


Illustration 30: Select the Lavender Swatch from the Palette

  1. Close the Properties dialog.

To conclude our exploration of the PivotChart, let's practice changing the view of the data.

  1. Resurrect the Field List once again.
  2. Select Product Name by clicking it.
  3. Select Series Area in the text box to the right of the Add To button.
  4. Click Add to.
  5. Close the Field List.
  6. Click the City selector button (just above the Legend we created earlier, on the right side of the chart).

The City selection drops down.

  1. Deselect All by clicking the checkbox to its left.

All cities become deselected.

  1. Select Boise as the sole city for analysis by placing a check in the box to its left.
  2. Click OK.

The selector dropdown closes.

  1. Right-click the Category Axis Title (Q1 / Q2 Results) box.
  2. Select Properties.

The Properties dialog appears.

  1. On the Format tab, modify the Caption to read: Q1 / Q2 Results by Location and Product.
  2. Close the Properties dialog.

The PivotChart appears, with our modifications, as depicted in Illustration 31.


Illustration 31: The PivotChart after Modifications

There are numerous other options and views we might select within our PivotChart to customize it for the myriad analysis requirements that we might encounter. The addition of the PivotChart to Access 2002 provides a great analysis and presentation tool to business analysts and other information consumers everywhere.

Conclusion ...

With this lesson we introduced the PivotChart in MS Access 2002, basing it upon a PivotTable view we created for that purpose. After a brief introduction to PivotChart views, and a comparison to the PivotTable views we explored in the previous lesson, we began to explore some of the ways that we can use the PivotChart for interactive data analysis. In preparing to show the various steps in using the PivotChart, we first created a query, which in turn supported a PivotTable, both as a review of the steps we took in the previous lesson and as a means of creating the basis for the PivotChart. Once we created the PivotChart, we explored ways to organize the new PivotChart view to make it easy to use. We then took steps to format and organize our view, and walked through the process of changing the chart type, examining various formatting procedures as we proceeded.

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

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