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.
 
Sort by date Sort by votes
Joe4 said:
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...
Click to expand...
Good point I hadn't thought of that. The structure is completely the same but the field name may be 'Date Closed' in one table and the other 'Completion Date' in another. I will change aliases so that they are all the same and see if that works. SQL below plus results from Union query and List query
 

Attachments

  • 2024-02-15_16-47-25.png
    2024-02-15_16-47-25.png
    12.8 KB · Views: 9
  • 2024-02-15_16-50-42.png
    2024-02-15_16-50-42.png
    97.3 KB · Views: 8
  • 2024-02-15_16-51-39.png
    2024-02-15_16-51-39.png
    89 KB · Views: 9
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
It might not like that ".*" in the Select portion too, not sure.
However, if you are adding aliases, I suppose you will be replacing the ".*" with each field anyhow, so we will see how that turns out.
 
Upvote 0
Joe4 said:
It might not like that ".*" in the Select portion too, not sure.
However, if you are adding aliases, I suppose you will be replacing the ".*" with each field anyhow, so we will see how that turns out.
Click to expand...
So, made sure all the field have the same aliases throughout each query and then created a union query asking for the aliases instead of *.
Works fine in Access showing stacked results but does not appear in list of queries in MS Query
 

Attachments

  • 2024-02-16_10-38-40.png
    2024-02-16_10-38-40.png
    41.1 KB · Views: 6
Upvote 0
OK, I am sorry. I guess that wasn't it then. Not really sure then.
 
Upvote 0
welshgasman said:
@Joe4 Sorry, not sure we have got to post #18 then? :(
Click to expand...
What I am saying is that I already made those suggestions back in post 6 (with the exact same link), and they did not want to go the Make Table route (see their reply in post 7).
 
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