Message from discussion How to use a union query in access as a data source for a pivot ta

Thread-Topic: How to use a union query in access as a data source for a pivot ta
thread-index: Acdw3L/gvn+9fo5EQZutq3VbgCvFdA==
X-WBNR-Posting-Host: 195.100.0.129
From: =?Utf-8?B?S2V2aW4gTg==?= <Kev...@discussions.microsoft.com>
References:  <1172238784.815483.130540@z35g2000cwz.googlegroups.com>
Subject: RE: How to use a union query in access as a data source for a pivot ta
Date: Tue, 27 Mar 2007 18:59:41 -0700
Lines: 83
Message-ID: <8F379BE5-DFED-4A2A-AB30-47EFA022B956@microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757
Newsgroups: microsoft.public.excel.querydao
NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
Path: g2news1.google.com!news3.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!t-online.de!207.46.248.126.MISMATCH!TK2MSFTFEEDS01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGHUB02.phx.gbl

Rob,
This holy grail has been quest so I am really greatful for your posting.

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
> 
>