Create a pivot table based on a union query

what version of excel do you have? I created a union query, and like you said, it doesn't show up. then I created a Select * Union query, and gave it a different name, and that one was listed, and I was able to bring it into excel with no problem

I can do the same, but when I try to pivot off the information everything goes to zero or it counts things in the wrong columns. It is clearly an undocumented feature.

I have a couple of suggestions:
1. Create a make table query using the Union Query as the source and then you can import the data from the new table into excel. You cannot convert a union query to a make table query. You must make a new query using the Union Query as the source.
or
2. Export the union query to excel from access and run your pivot table.

HTH

Alan

Rule# 1- Do not use double quotes in your union query anywhere.
It may work in access but it will screw things up in the Jet data engine. It will also cause the "too few parameters" error.

Rule #2- Use ms query to create the pivot table. To do this go to the data tab--> from other sources --> from ms query. Choose ms access database. Select your query and data fields. Import Data into a pivot table.

For some reason union queries are not visible using the insert-->pivot table--> use external data range method.

Tip #1 - you can add your union query to a select query and then add other related data fields to the data set. If you have calculations to make, I advise you to do them in the select query. Calculated fields created inside the pivot table tool are subject to more restrictions.

Good luck

Toom21 (using excel2007+access2003)