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, 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!
> 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, > 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.
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.
> 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?
|
