Create a PivotTable with data from Access

The beauty in creating pivot tables is that data for your “PivotTable” need not come from an Excel file.
We can retrieve the data from a database.
In this example, I will retrieve the data from an “Access” database, the Northwind database to be exact. Anyone who knows a bit of “Access”, will be familiar with this sample database.

Before we start we must know which data in the database that we want in our “Pivot Table”.
The Northwind database consists of several tables and queries. If you want to insert another database, choose the query “Product Sales for 1997” as an example.
To make a pivot table out of a database like Access this database most NOT be opened.

Click the “PivotTable” button in the “Insert” tab at the top of the “Ribbon”.
Select “Use an external data source” in the “Change PivotTable Data Source” dialog.
And click the “Choose Connection” button.
In the “Existing Connections” click the downward pointing arrow and choose “Connection files on this computer”.

In the next dialog box, click the “Browse for more” button, and navigate and select the file, in my case the Northwind.mdb database.
Click OK to close the dialog box.
I select “Product Sales for 1997” from the list and click OK.
Click OK again to the next dialog box.

All fields from the “Product Sales for 1997” query in the “Access” file are displayed in the “PivotTable Field List”.

The only thing you have to do is place the PivotTable fields in different areas.

If you wish to retain this file, don’t forget to save.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats