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

July 1, 2003

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:

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

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

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.

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

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

The existing queries appear, as shown in Illustration 4.

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

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

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

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

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.

• CategoryName
• Description

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

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

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

The Save As dialog appears.

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

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.

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.