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:
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:
1. Go to the Start button on the PC, and then navigate to the Microsoft Access icon, as we did in Lesson 1: Create a Calculated Field with the Expression Builder.
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, 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.
5. Click OK.
The splash screen disappears, and is replaced by the Main Switchboard, as shown in Illustration 2.
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.
7. Click Queries, under Objects in the Database window.
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.
8. Click the New button, shown circled in Illustration 4 above.
The New Query dialog appears, as shown in Illustration 5.
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.
10. Select the following tables, highlighting each, and then clicking the Add button, to add each successively to the Select Query dialog.
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.
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:
13. From the Orders table, select:
14. From the Order Details table, select:
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.
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.
The query runs, and returns the data set, whose size is 2,155 rows, as partially displayed in Illustration 10.
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.
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.