> Unfortunately when I try this the Data Connection Wizard - Select Database
> and Table screen (your step 11) does not display any select queries that
> select from a SQL union query!

> Do you have any suggestions?

> "Rob" wrote:
> > 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