Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jul 1, 2003

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

By William Pearson

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:

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.

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.

5.             Click OK.

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:

  • ProductName

16.         From the Categories table, select:

  • CategoryName
  • Description

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.

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM