MS Query and Access Union Query

[Neil-D]

Newish to MS Access and struggling
I have an MS Access with several tables and I have created a Union query (union_Results) that combines them into 1 stacked query. I have then created a list query (qry_Results) that shows all the stacked results.
When I view it in MS Access it works fine, showing all the results OK.
When I go to MS Excel and try to import the query qry_Results in, it is not shown in the list of queries, tables.
All my other queries are there, just not qry_Results.
If I change the source of qry_Results to any other table/query then it appears.

What am I doing wrong?
Thanks in advance for any help.

 

[ranman256]

it's possible the query is too complex for Excel to use so it ignores the object.

 

[Joe4]

I have an MS Access with several tables and I have created a Union query (union_Results) that combines them into 1 stacked query

Note that this is sometimes indicative of a design flaw with your database.
If you have multiple tables of similar structure, they question is why?

If possible, there should probably be just one table, with all this data in it, if all the individual tables are of similar structure.
If all the data is in one table, then you probably wouldn't need Union Queries.

 

[Neil-D]

Note that this is sometimes indicative of a design flaw with your database.
If you have multiple tables of similar structure, they question is why?

If possible, there should probably be just one table, with all this data in it, if all the individual tables are of similar structure.
If all the data is in one table, then you probably wouldn't need Union Queries.

The tables are from Global systems that I have linked tables for so have no control over. Each table is from different system that link activities/work done. So I have queries that just show data for UK, name of person doing activity and date plus new field for activity being done. The union then stacks the queries so I have one list showing the name of the activity being done, UK person doing the activity, date of activity. This is the only way I can think of, of creating a list of activities for UK people.

 

[Neil-D]

Note that this is sometimes indicative of a design flaw with your database.
If you have multiple tables of similar structure, they question is why?

If possible, there should probably be just one table, with all this data in it, if all the individual tables are of similar structure.
If all the data is in one table, then you probably wouldn't need Union Queries.

it's possible the query is too complex for Excel to use so it ignores the object.

Each query is only filtering by Country to UK, and then listing a couple of fields - person's name and date of activity plus a new static field with activity. 8 activities each with a result of 50 - 60 records. This shouldn't be too complex a query.

 

[Joe4]

Each query is only filtering by Country to UK, and then listing a couple of fields - person's name and date of activity plus a new static field with activity. 8 activities each with a result of 50 - 60 records. This shouldn't be too complex a query.

I don't think it is due to the complexity of the query, but rather just by the fact that is a Union Query.
If you do a Google search, you can see that other people had the same issue.
Here is one article that describes the situation and some solutions:

Redirecting

Another workaround option is to have your Union Query write to a Table (with a Make Table Query or Append Query), and have Excel reference that table instead of the Union Query.
The caveat there is that is not quite as dynamic, and you have to run this Make Table/Append Query anytime the data changes.

 

[Neil-D]

I don't think it is due to the complexity of the query, but rather just by the fact that is a Union Query.
If you do a Google search, you can see that other people had the same issue.
Here is one article that describes the situation and some solutions:

Redirecting

Another workaround option is to have your Union Query write to a Table (with a Make Table Query or Append Query), and have Excel reference that table instead of the Union Query.
The caveat there is that is not quite as dynamic, and you have to run this Make Table/Append Query anytime the data changes.

Thanks, I googled this and came to the conclusion that the Union was causing the issue and wondered if anybody how found a way around it.
I had also seen the solution of using a MakeTable query but this will not be practical as this is pulling data from Global systems and updated often.
I could create an MS Query for each Activity to bring them into Excel and then some how combine them in MS Excel but it doesn't feel very 'neat' as this is exactly what a Union query does.

 

[Joe4]

Thanks, I googled this and came to the conclusion that the Union was causing the issue and wondered if anybody how found a way around it.
I had also seen the solution of using a MakeTable query but this will not be practical as this is pulling data from Global systems and updated often.
I could create an MS Query for each Activity to bring them into Excel and then some how combine them in MS Excel but it doesn't feel very 'neat' as this is exactly what a Union query does.

Did you take a look at the link I provided?
Someone appears to have suggested a solution there, which seems to allow you to access the Union Query in Excel (the first reply from MustAddict).
It looks like it involves changing how you connect to the Access database.

 

[Neil-D]

Did you take a look at the link I provided?
Someone appears to have suggested a solution there, which seems to allow you to access the Union Query in Excel (the first reply from MustAddict).
It looks like it involves changing how you connect to the Access database.

I did, but when I use 'Other Sources' the MS Query shows the query but does not list any columns. All the buttons are greyed out and the button by the query name is a minus sign whereas all the others queries are a plus and expand to show the fields/columns to select.

 

[Joe4]

Can you post the SQL code of your Union Query?

Do the columns you are returning in it all have the same name?
If not, you may want to try using Aliases to make sure that they all have the same name.
I wonder if they have different names, that is what is causing the issue, as it doesn't know how to refer to each column.

Just a guess...