Is there a limit on Union query unions?

[Guest]

the following is just a part of a sql for a union query:
***********************************
SELECT "a.4. Roofing - Membrane, Built-up, etc." AS Subsystem, "0_Backlog"
AS [Year], SumOfBacklog AS [Dollars]
FROM qrygraphForecastbySubsystem_1
Where Subsystem = "a.4. Roofing - Membrane, Built-up, etc."

Union All

SELECT "a.4. Roofing - Membrane, Built-up, etc." AS Subsystem, "2008" AS
[Year], SumOf2008 AS [Dollars]
FROM qrygraphForecastbySubsystem_1
Where Subsystem = "a.4. Roofing - Membrane, Built-up, etc."

Union All

SELECT "a.4. Roofing - Membrane, Built-up, etc." AS Subsystem, "2009" AS
[Year], SumOf2009 AS [Dollars]
FROM qrygraphForecastbySubsystem_1
Where Subsystem = "a.4. Roofing - Membrane, Built-up, etc."

Union All

SELECT "a.4. Roofing - Membrane, Built-up, etc." AS Subsystem, "2010" AS
[Year], SumOf2010 AS [Dollars]
FROM qrygraphForecastbySubsystem_1
Where Subsystem = "a.4. Roofing - Membrane, Built-up, etc."
***********************************************
When I exceeded 8 unions the query did not run, instead, I got an error
message saying that the query was too complex to run. I had to make 2
separate union queries that do not exceed 8 unions each. The total unions
that I needed was 13.
Does any one know what may be causing the problem. I have a feel that there
is no limitation on the number of unions that I could have in a union query.
it may by my sql.
thanks
Al

 

[John Spencer]

Yes there is a limit on the number of queries you can use in a union query.
I don't know that it is a hard and fast number, but believe it is more
dependent on how complex the query gets when the interpreter tries to turn
it into a query.

Somthing really short

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

 

[George Nicholson]

There may well be a limit, but I don't recall ever running into it.

What I generally do is create a separate saved query for each part of the
Union, giving them similar names and tacking on a number or descriptor to
the end (so they would appear as a group in the database window). Then
simply reference those queries in the UNION. I know I've far exceeded 13
with that approach. This is also makes things a lot easier to debug and
maintain. If you get an error message, you run the individual queries
one-by-one to see where the problem pops, rather than driving yourself nuts
with a huge glob of SQL in the UNION itself. Likewise, if you need to add or
modify a single portion to the final dataset, this 'modular' approach makes
it a much less onerous task.

HTH,

 

[John W. Vinson]

I have a feel that there
is no limitation on the number of unions that I could have in a union query.

There is a limit, but not on the number of UNIONs per se: instead, it's on the
total size of the compiled query. I believe that the limit is 64KBytes. I'd
suggest following George's suggestion of UNIONing stored queries by name. An
alternative which can help some is to trim the size and complexity of each
query as much as possible, by omitting unneeded table references, using short
alias names, etc. Not sure that will help but... why are you defining
Subsystem as a calculated field and using it as a criterion too!? Why not just
use

SELECT Subsystem, "2008" AS [Year], SumOf2008 AS [Dollars]
FROM qrygraphForecastbySubsystem_1
Where Subsystem = "a.4. Roofing - Membrane, Built-up, etc.";

if Subsystem is a field in the query?


John W. Vinson [MVP]