Create a “Pivot Table” with data from “Access”

The beauty of creating “Pivot Tables” is that it is not necessary for data of your “Pivot Table” to come from an “Excel” file.

Information can be retrieved from a database as well.

In this example, I will retrieve data from an “Access” database, the “Northwind” database to be exact. For anyone who knows a bit about “Access” sample databases, this will sound familiar.

Before we start we must know which data from the database we want in our “Pivot Table”.

The “Northwind” database consists of several “Tables” and “Queries” but the process explained below will not be different if you want to insert data from another database.

I choose the “Extended Customer” query as an example.

To make this “Pivot Table” you must NOT open the query.

Click the “Pivot Table” button in the “Insert” tab at the top of the “Ribbon”.

Select “Use an external data source” in the “Create Pivot Table” 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 Database2accdb database, which is in the “Northwind” database.
Click “Open” to open the dialog box.

In the next dialog box, click OK again.

In the next window select the “Table” you want to insert. In this case, the “Extended Customer” Query, and click the OK button.

Click OK again to go to the next dialog box.

All fields from the query “Customer Extended” from the “Access” file are displayed in the “Pivot Table Field List”.

 

The only thing you have to do is place the “Pivot Table” fields in the different areas.

Remember to save your file, if you wish to retain it.

Featured Courses

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