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

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.

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; if so, close it by clicking OK.

The Main Switchboard appears.

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

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

The existing queries appear.

7. Click the New button atop the window.

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

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

9. 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
10. 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.