Message from discussion
How to use a union query in access as a data source for a pivo
Thread-Topic: How to use a union query in access as a data source for a pivo
thread-index: AcdxSBc4RbsPGH8NSA+vcs3ZPuki7g==
X-WBNR-Posting-Host: 195.100.0.129
From: =?Utf-8?B?S2V2aW4gTg==?= <Kev...@discussions.microsoft.com>
References: <1172238784.815483.130540@z35g2000cwz.googlegroups.com> <8F379BE5-DFED-4A2A-AB30-47EFA022B956@microsoft.com> <1175086022.134993.245130@o5g2000hsb.googlegroups.com>
Subject: Re: How to use a union query in access as a data source for a pivo
Date: Wed, 28 Mar 2007 07:48:04 -0700
Lines: 112
Message-ID: <01BA33FB-F20A-48C7-80E1-21E8957EF5C9@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!news1.google.com!feeder3.cambrium.nl!feed.tweaknews.nl!feed10.multikabel.net!multikabel.net!feed20.multikabel.net!newsfeed.freenet.de!news-lei1.dfn.de!news-ham1.dfn.de!news.zfn.uni-bremen.de!informatik.uni-bremen.de!newsfeed.cw.net!cw.net!news-FFM2.ecrc.de!TK2MSFTFEEDS01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGHUB02.phx.gbl
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
>
>
|
