Max Number of Tables in Union Query?

[Therber]

Is there a maximum number of tables you can use in a union query? I keep encountering an error whenever I go beyond 20 tables.
Thanks.

 

[Lightning]

I'm not aware of a limit on the number of TABLES, but I know there is a limit of 255 FIELDS in any one query or table.

The Access Help file does have information on limitations, but it is very obscure. In Access 97, read the general topic on Indexes for some information on limitations.

In your particular case, my suggestion would be to break the query down into a number of discrete queries, and then join those queries to achieve your final result. Twenty tables in one query must make it V-E-R-R-R-Y hard to read and edit.

HTH
Lightning

 

[MichaelRed]

In your particular case, my suggestion would be to break the query down into a number of discrete queries, and then join those queries to achieve your final result. Twenty tables in one query must make it V-E-R-R-R-Y hard to read and edit

Yes -BUT- the limit of 255 "fields" remains, so if you put 11 "tables" into one Union Query and another 10 in a second Union query you will still run afoul of the number of fields. A way out of this is to change ONE of the queries to a MAKE TABLE query, and then change the remainder to Append queries. If you use the info more than a few times it is a better approach anyway, because the Union()s) are slow anyway.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over