How to use a union query in access as a data source for a pivot table
Dear all,
For some years I have been using MS Access to manipulate data into a
required format and then displaying it using Pivot tablesin Excel .
The thorn in my side with this approach has always been union queries
which, except in certain circumstances, will throw an error (too many
parameters. expected n) when you try to connect using ODBC and MS
Query. I have used the workaround that union queries are allowed if
all the fields are numbers, i.e. no text fields.
However, as of today, I have discovered a solution in Excel 2003/
Access 2003 (which possibly also works in Excel XP/Access XP but I
haven't checked) which allows you to connect directly to a union query
and display the results in a pivot table. This is my own personal
holy grail so I thought I'd share it.
The problem with union queries is down to the MS Access ODBC driver
not dealing with them correctly, so the solution is to avoid using
ODBC. In its place we can use OLEDB, but that throws up the problem
of which OLEDB driver to use. The Access OLEDB driver doesn't support
union queries either BUT the Jet 4.0 OLEDB driver does. However, in
order for the OLEDB driver to be able to see the union query we have
to dress it up in a simple select query because it ignores everything
but tables and select queries. The final part of the problem was how
to get the data straight into a pivot table. This bit is simple
enough just by following the instructions in XL - rather than using
the pivot table wizard, use the get external data wizard and then
select to show the results in a pivot table.
So that's the long and short of it, here is the process to link xl to
a union query:
1. Create your union query in Access (called MyUnion for this
example)
2. Create a select query along the lines of 'Select * from
MyUnion' (called SelectMyUnion)
3. In Excel, select Data\Import External Data\Import Data...
4. Select '+Connect to New Data Source.odc'
5. From the list of data source types, select 'Other/advanced'.
Click next.
6. From the list of providers, select 'Microsoft Jet 4.0 OLE DB
Provider'. Click next.
7. Click the ... button to select the Access database that contains
SelectMyUnion.
8. Enter any details you need to log in to the database, or just
leave it blank if there aren't any.
9. Click 'test connection' to check it's working. If not, check your
account details and the db
10. Click OK. (you may have to wait a while for the next dialog box to
appear - not sure why)
11. Select 'SelectMyUnion' from the list of specific tables OR just
untick the box. Click Next
(note that if you untick the box, you will be prompted to select a
table whenever you use the connection)
12. Give it a filename, e.g. MyUnionQuery.odc, and anything else you
want . Click Finish.
13. If you didn't select a table in step 11, do so now. Select
'SelectMyUnion', click OK.
14. On the import data dialog, select 'create a pivottable report'.
15. You are now magically at step 3 of the pivot table wizard where
you can carry on as normal
16. Click Finish and hey presto, there is your union query data in a
pivot table.
17. If you need to use the connection again, you can just select the
saved .odc file from the Import Data bit (step 3).
I knew there had to be a way to do it. Hopefully this will save
someone else 4 years of mucking about!
Rob
date: 23 Feb 2007 05:53:04 -0800
author: Rob
