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
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
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
We'll start Access and proceed, taking
the following steps:
opens, and may display the initial dialog. If so, close it.
-> 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.)
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
splash screen disappears, and is replaced by the Main Switchboard, as
shown in Illustration 2.
Illustration 2: Inside Access, Northwind Main Switchboard
Click the Display
Database Window, or get there by an alternative approach.
arrive at the Database Window, which appears as depicted in Illustration
Illustration 3: Inside Access, Northwind Main Switchboard
under Objects in the Database
queries appear, as shown in Illustration 4.
Illustration 4: Queries in the Northwind Database (New
NOTE: The queries that appear in your
individual view may differ, depending upon past activities within the sample
Click the New
button, shown circled in Illustration 4 above.
Query dialog appears, as shown in Illustration 5.
Illustration 5: The New Query Dialog
the Design View option is selected, click OK.
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
following tables, highlighting each, and then clicking the Add button,
to add each successively to the Select Query dialog.
Click the Close
button on the Show Table dialog to close it.
Query dialog displays the newly added tables, appearing as shown in Illustration
Illustration 7: The Select Query Dialog, Selected Tables
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.
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
From the Customers
From the Orders
From the Order
Details table, select:
From the Product
From the Categories
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)
run the query and examine the result set that it returns.
--> Run from the main menu, as shown in Illustration
Illustration 9: Select Query -> Run to Execute the Query
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
--> Save As.
As dialog appears.
into the Save To field.
from the dropdown selector for the As field.
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.
--> Close to close the Customer_Orders_Query.
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.