UPDATE: How to use a union query in access as a data sourc..

Hi Everyone,

This is an update to the system I outlined in the post at:

http://groups.google.co.uk/group/microsoft.public.excel.querydao/brows...hread/t

which deals with pulling in data from a union query in access through
to a pivot table in excel.

The instructions included a step (2 and 11) to create a 'query mask'
so that the union query showed up in the dialog box. A couple of
posters had problems with this and in fact I now realise this is
unnecessary. In actual fact it's much easier to do that my original
post suggested because you can use SQL in the connection rather than
having to pick a table/query.

The trick is to:

- don't connect to a specific table/query during the set up of the
connection (untick the box)

- after saving the connection, you're asked to pick a table/query -
just pick any old one (best to pick a small one for speed purposes
though)

- then, at the 'import data' dialog (the one where you can choose to
'create a pivot table report'), just click 'Edit Query' in the bottom
right corner.

- in the 'Edit OLEDB query' dialog, change the command type to 'SQL'
and enter your SQL directly, e.g. Select * From MyUnionQuery.

- in fact you can even just leave the command type as 'table', and
type the name of your union query in the command text.

The command text appears to allow any kind of Access SQL, so you can
do union queries direct there, or use access functions, or whatever
you like. If you want to, use the QBD view in Access to write the
query and then paste the SQL across.

And the cherry on the top, at any point if you want to edit the SQL,
just make the pivot table active, select data\import external data
\edit query and away you go...

Easy when you know how! Perhaps MS could make this the default route
to creating a pivot from external data - it certainly seem more
intuitive to use the Jet OLEDB (which includes most Access
functionality) rather than nasty ODBC.

Rob