MS Query and Access Union Query

N

Neil-D

New Member
Joined
Dec 9, 2009
Messages
29
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Neil-D said:
I have an MS Access with several tables and I have created a Union query (union_Results) that combines them into 1 stacked query
Click to expand...
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.
 
Upvote 0
Joe4 said:
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.
Click to expand...

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.
 
Upvote 0
Joe4 said:
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.
Click to expand...

ranman256 said:
it's possible the query is too complex for Excel to use so it ignores the object.
Click to expand...

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.
 
Upvote 0
Neil-D said:
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.
Click to expand...
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:

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.
 
Upvote 0
Joe4 said:
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:

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.
Click to expand...
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.
 
Upvote 0
Neil-D said:
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.
Click to expand...
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.
 
Upvote 0
Joe4 said:
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.
Click to expand...
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.
 

Attachments

  • 2024-02-15_16-10-56.png
    2024-02-15_16-10-56.png
    9 KB · Views: 7
Last edited:
Upvote 0
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Access - How do you change a shared drive path name to not include individual user so others can run the queries?
Replies
2
Views
221
welshgasman
welshgasman
Anigito
  • Solved
MS Access Union Query Changes Numeric values to Text values
2
Replies
11
Views
989
Anigito
Anigito
A
  • Solved
Query Criteria: First attempt
Replies
3
Views
333
AK77304
A
J
  • Question
Access query not updating excel pivot properly
Replies
1
Views
454
Micron
M
tourless
  • Question
Pain in the Access
Replies
6
Views
666
alansidman
A

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top