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.

 

[welshgasman]

Jusr had a thought.
Have you tried a simple select query that has your union query as it's source?

 

[james_lankford]

> The tables are from Global systems
how are you getting those tables

I do something similar but with tableau

I use MS Access to do 8 passthrough queries
each passthrough goes to a different MS SQL Server
each SQL Server has a database structure that is exactly the same
its just that one server is for the north region, one is for the south region and so on ...

so passthrough_1 just does
select 50 columns from tableX where blah blah blah

and passthrough_2 just does
select 50 columns from tableX where blah blah blah

and passthrough_3 just does
select 50 columns from tableX where blah blah blah

I have 8 of those each going to a different sql server
then I create one normal access query named
list_tableX
all it does is
select * from
(
select * from passthrough_1
union all
select * from passthrough_2
union all
select * from passthrough_3
...
select * from passthrough_8
) as tbl

then I open tableau and select Access as my connection and list_tableX as my datasource
it works perfectly , and fast

 

[welshgasman]

Just tried what I suggested in post #21, and that works?, so a resonable workaround?

 

[Neil-D]

Jusr had a thought.
Have you tried a simple select query that has your union query as it's source?

Yes, that is the query I am trying to use and does not appear in the MS Query list. Neither does the Union query

 

[Neil-D]

> The tables are from Global systems
how are you getting those tables

Tables linked to external Sharepoint tables (Globally controlled), then a query for each pulling the data I need, then a Select query standardising the data columns, then a Union query stacking all the data and then a Select query as an exact copy of the Union query - hope this isn't too confusing, can't think of anyway of showing this.

 

[Neil-D]

> The tables are from Global systems
how are you getting those tables

I do something similar but with tableau

I use MS Access to do 8 passthrough queries
each passthrough goes to a different MS SQL Server
each SQL Server has a database structure that is exactly the same
its just that one server is for the north region, one is for the south region and so on ...

so passthrough_1 just does
select 50 columns from tableX where blah blah blah

and passthrough_2 just does
select 50 columns from tableX where blah blah blah

and passthrough_3 just does
select 50 columns from tableX where blah blah blah

I have 8 of those each going to a different sql server
then I create one normal access query named
list_tableX
all it does is
select * from
(
select * from passthrough_1
union all
select * from passthrough_2
union all
select * from passthrough_3
...
select * from passthrough_8
) as tbl

then I open tableau and select Access as my connection and list_tableX as my datasource
it works perfectly , and fast

why would pulling the data in via a Passthrough query and then using a Union query change that MS Query does not list the query? Rather than linking to External data and then using a Union query? All my queries are visible to MS Query except for any that use a Union query anywhere in them.

 

[welshgasman]

I think we have extablished that Union queries to not appear to MS query. Thatnis why I suggested using that as a source for s imple select, and that works on my setup. However I am just using local tables.
However I have just gone in again to check and I never noticed that my union query is actually visible to Excel? I am using 2007.

My union query is qryUnion and the imternediate query is qrySelectUnion, both which are visible as you can see.

Try local tables a test. It only takes a few minutes.

 

[Neil-D]

I think we have extablished that Union queries to not appear to MS query. Thatnis why I suggested using that as a source for s imple select, and that works on my setup. However I am just using local tables.
However I have just gone in again to check and I never noticed that my union query is actually visible to Excel? I am using 2007.

My union query is qryUnion and the imternediate query is qrySelectUnion, both which are visible as you can see.

Try local tables a test. It only takes a few minutes.
View attachment 107092

I have tried that as well but cannot get the query to show.
However, I do have a sort of answer to it all. All the queries that MS Query shows in its list are the ones I created in the MS Access when it was stored on my c: drive. As per Corporate policy all files have to be stored via MS Teams in a OneDrive system to allow for backups. Anything I create in the MS Access database, now that it is stored on OneDrive, does not show up in MS Query. Even if I save it back on my c: drive, all items created after it was stored on the OneDrive are not accessible by MS Query. Even if created when it is back on the c: drive. I am certain this is something to do with our Global IT security system encrypting the data and another tool like MS Query cannot see the items now. Nothing I can do to get any answers from Corporate as any questions on IT security will be blocked.
So I cannot fix this except by starting the db from scratch and keeping it on my c: drive to stop encryption, but will then go against IT Policy. Thanks everyone for your suggestions and my guess is that most should have worked if it wasn;t for Corporate.

 

[mdmilner]

Just a thought for a test. What if you created a 2nd Access Database, then created a linked object in it that links into the "suspected encrypted" database?