Union Query - Maximum tables

[mbentley]

Is there a maximum number of tables and/or queries that can be in a union query?

 

[Pat Hartman]

There is but it is not published. It depends on the complexity of the queries and the number of columns selected. I have run into problems with around 10 queries. I broke the union into two parts to union 5 tables each and then unioned those queries. I'm not sure how far you can go with this. It solved my problem so I had no need to investigate further.

 

[mbentley]

Thanks. I was able to bring 13 small queries into one, but I then need to bring 4 of these union queries into a single, which would ultimately be 52 queries. Access didn't like that.

 

[Pat Hartman]

If this is a one time conversion, you can do a make table query and then a bunch of append queries rather than a single huge union query.

If this is for an ongoing process, you should normalize your tables so you don't have to do it on the fly.