Did you know you can analyse data from an Access database in an Excel Pivot Table without importing the data first?
If you use Access then this is a great productivity booster because once you connect to your Access query you simply refresh the data connection and your Pivot Table updates, as do any charts or reports you have connected to the Pivot Table data.
Almost all Access databases consist of more than one table, so if yours is like this then you need to build a query in Access that brings together all the data you want to analyse in your Pivot Table.
Then you simply insert a Pivot Table and select the 'Use an external data source' option and click the ‘Choose Connection’ button.
You can then browse to your existing connection:
Once you have selected your file you can select the query you want to connect to:
Note: Remember, if your Access database has more than one table in it you will need to build a query first and then connect your Pivot Table to the query.
Tip: Since Excel imports the data in the query into the Pivot Cache it is recommended that you only include data in the query that you need, as unnecessary data will generate a large, slow file.
Once you click OK it may take a while for Excel to import all of the data into the Pivot Cache. If your Access query is large then this may take a few minutes, depending on the processing power of your PC and the location of the database.
Now you can Pivot away to your heart’s content…
and while you’re there you can add a PivotChart or other reports from the data.
Then each week/month/quarter simply refresh the Pivot Table (CTRL+ALT+F5) to update your reports.
Refreshing your Pivot Table using CTRL+ALT+F5 automatically connects to Access and brings in the new data. Job done 🙂
It's probably not even time for morning tea yet, but I'm sure your boss won't mind if you take a break since you've got your work done in record time.
Tip: Importing data into Excel isn't limited to Access. You can import data from the web, SQL Servers and other sources you may have access to.
The ability to connect directly to external data sources and analyse them with Pivot Tables eliminates the tedious tasks associated with importing data like copy, paste, formatting and complex formulas, not to mention the ongoing maintenance of this data as it grows monthlty and requires updating.
Get over 10 hours of comprehensive Excel online training, including tutorials on Excel Pivot Tables and Importing External Data into Excel.
This is so wonderful! 2 million record file – could not open in Excel – just linked to Access! Very cool.
L.E.: That would be cool. 🙂
Fantastic, Christine! Glad I could help.
I have a question on MS access.
I am given a task to use a expediting report (materials from procurement) on which I need to summaries the data based on the materials & qty and vendor & qty (25000 rows).
First I used excel pivot to summaries the data, where I dropped on the column field by “design area” and row field “material code” and qty as values. tht is fine, I wanted to include two new row fields “condition1 & Condition2” which should be repeated for each “material code” I dropped in row field previously. problem is these 2 conditions (1 & 2 ) sometimes is present in the source data or sometimes anyone condition is present (either 1 or 2). But I want both condition to be repeated for all material code in pivot table and leave a blank row if values are not there?
I am using access also to solve this problem w/o too much manual effort. I am not able to solve it. Can you please help!
Hi Jeevan,
Have you tried to check the ‘Show items with no data’ on the Layout and Print tab in the Field settings?
Right click the PivotTable > Field Settings > Layout & Print tab.
I hope that helps.
Kind regards,
Mynda.
It looks like part of your Access database is the same as that on a MS website with a Pivot Table tutorial. Is this data copyrighted or are there any limitations on its use?
Hi Chuck,
I’m sure there are limitations on its use, however it’s there to aid in learning so I would ‘think’ if it’s being used for that purpose then it’s not in breach….or if it is it’s not in Microsoft’s interest to do anything about it.
Cheers,
Mynda.
Hi
I used this method however it wont display all of the available queries. I have 18 queries in my DB but excel will only show me 6?
any ideas?
Thanks
Hmmm…not sure why it would be doing this, James. Sorry.
Is there a way for the excel pivot table to pick up the query caption names versus the field names?
Hi Heather,
Not that I can find, unfortunately 🙁
Kind regards,
Mynda.
I want to export multiple queries through one connection. Any idea how to go about this?
Hi Nitu,
Have you tried embedding the queries you want on the relevant worksheets in Excel and then simply refreshing them when you need?
To create a link to your Access database from within Excel on the Data menu choose From Access. Once the queries are set up, you can refresh all by clicking CTRL+ALT+F5 or click the Refresh All button on the Data tab of the Ribbon.
Kind regards,
Mynda.
I connected Excel pivot tables to my Access database as described above and everything worked great. However, when I made a change to the field order in the linked query, the pivot refresh did not work properly. The refresh finished, but the data was in the wrong columns. For example, if before my query consisted of FirstName, LastName, MiddleName, and I changed the query to be First/Middle/Last, the refresh did not update the Field List Order and resulted with last names in the MiddleName column and middle names in the LastName column.
Is there a way to force the Field List order to refresh in addition to the data?
Thanks!
Hi Kelly,
I tested it with my Access database and PivotTable and it refreshed correctly. Maybe something was corrupted with yours. Have you tried to rebuild the query?
Kind regards,
Mynda.