Maximum size of Union Query?

[nyrblue2]

I have been using a Union Query to combine multiple tables with identical
fields. In a visual sense, I have "stacked" many tables on top of each
other. However, I got to a certain point where I had apparently added too
many SELECT statements to my query and recieved an error about my query being
"too complex". Is there a limit to the number of SELECT statements that can
be used? Is there a limit to the total number of fields added (each of my
SELECT statements calls 4 fields)? Is there a limit to the numbers of
results that this Union Query can produce (maximum number of "lines")?

 

[John Spencer]

Yes, Yes, and No.

There is a limit on the total number of Select statements. I'm no sure
whether the limit is on the length of the statements or the number or some
combination

I believe there is a limit on the number of fields that you can use (255?)

There is no practical limit on the number of rows that can be returned.

You may have to resort to building and populating a table with the data from
your many tables in order to get them all into one place.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[nyrblue2]

Unfortunately, I don't think manually populating a table will be feasable
since the tables that the query pulls from are constantly changing and being
updated.

When I removed 2 of the SELECT statements, which left me with 38 SELECT
statements, the query was able to run. 38 seems like such an arbitrary
number. That is why I thought it might be related to the number of records
that are returned.

Since I am using 4 fields from each table, that gives me 152 total fields.
This is much less than the 255 you thought of. Plus, with all 40 SELECT
statments included, it would only be 160 anyway. Again, this seems like a
very arbitray number.

Any other thoughts on this? Has anyone else experienced this error?

 

[RogerManiccia@ebs]

You may want to investigate whether there is a 64k limit on the "file size"
of the union query text? I am not sure whether this is actually a limitation
in this instance, but it is in other places.