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.
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.
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:
16.
From the Categories
table, select:
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.
» 
Comment and Contribute
