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

Path: g2news1.google.com!postnews.google.com!r56g2000hsd.googlegroups.com!not-for-mail
From: "Rob" <rsjh...@googlemail.com>
Newsgroups: microsoft.public.excel.querydao
Subject: Re: How to use a union query in access as a data source for a pivo
Date: 28 Mar 2007 11:59:21 -0700
Organization: http://groups.google.com
Lines: 119
Message-ID: <1175108361.447989.293230@r56g2000hsd.googlegroups.com>
References: <1172238784.815483.130540@z35g2000cwz.googlegroups.com>
   <8F379BE5-DFED-4A2A-AB30-47EFA022B956@microsoft.com>
   <1175086022.134993.245130@o5g2000hsb.googlegroups.com>
   <01BA33FB-F20A-48C7-80E1-21E8957EF5C9@microsoft.com>
NNTP-Posting-Host: 81.1.88.106
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1175108363 25850 127.0.0.1 (28 Mar 2007 18:59:23 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 28 Mar 2007 18:59:23 +0000 (UTC)
In-Reply-To: <01BA33FB-F20A-48C7-80E1-21E8957EF5C9@microsoft.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.8.1.3) Gecko/20070309 Firefox/2.0.0.3,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: r56g2000hsd.googlegroups.com; posting-host=81.1.88.106;
   posting-account=OnixyQ0AAAAbJ1ZoZvbGhbqx3Rtt-jNk

On Mar 28, 3:48 pm, Kevin N <Kev...@discussions.microsoft.com> wrote:
> Thanks for your prompt response.
>
> I am using MS Excel 2003 querying a MS Access 2003 desktop .mdb application
> which is sat on a network server.
>
> I have several select queries using a SQL union query as their data source.
> Non of these select queries are visible from the connection wizard.  Tables
> and queries not associated with the SQL union query are visible.
>
> I am currently using a make table query which I run via an event on my Excel
> file to update a table prior to refreshing the Data Query on the worksheet
> which is pointed at the table.  This works but is not very elegant hance my
> interest in your break through. I can understand your satisfaction.
>
> "Rob" wrote:
> > On 28 Mar, 02:59, Kevin N <Kev...@discussions.microsoft.com> wrote:
> > > 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
>
> > unfortunately, not really.  If you have followed the instructions
> > above and are using XL 2003 with Access 2003 then I'm really not
> > sure.  I've had a look through the process again and there are no
> > settings I can find that would hide the queries.
>
> > Can you see the other select queries in your database that are based
> > on local tables?
> > Is the 'select query' that looks at the 'union query' a 'pass-through
> > query' because I'm not sure p-t queries show up.
>
> > If you give me a bit more info on your setup, I'll see what I can do.
>
> > Rob

hmm - that is a strange one - it sounds exactly like what I do.  From
what you write, it sounds like you have a number of 'complex' select
queries.  Have you tried doing a simple "Select * from UNION" to see
if that works?

Rob