Error Message - "Query is too complex"

[Guest]

I have approximately 40 databases each containing over 20,000 records and
have tried to use Union Query to combine as they all have the same layout and
fields. When I try to run the query an error message "Query is too complex"
is displayed. I have also tried to filter the records to include only
current year info and it still displays the message.

Is there a limit to what I can combine using union query?

 

[JohnFol]

It sounds like you might be hitting a limit for the length of SQL statement.

Are you doing a

Select * from Table1
Union all
Select * from Table2
:
Select * from Table40

?

 

[Guest]

Yes, I am using the sql statements you listed. How can I tell if I am
hitting the limit? Thanks for the reply.

 

[JohnFol]

a quick test qwould be to break it down into smaller queries, ie
Query1 would contain
Select * from Table1
Union all
:
Select * from Table20

Query 2 would contain

Select * from Table21
Union all
:
Select * from Table40

And finally do a Select * from Query1 union all Select * from query2

 

[Fresh Prince via AccessMonster.com]

I work also that way. Sometimes i've got huge nested querie and i get that
message too. I just split them in a couple queries and union them
afterwards.

 

[Guest]

I did try splitting into two queries and combining those two afterwards and
still received the same message. I will review my coding again and if it
still does this, can I post my code to see if you can find any problems with
it? Thanks.

 

[Guest]

Here is my code for Query 1:

SELECT *
FROM [GLAFS - Amarillo]

UNION ALL SELECT *
FROM [GLAFS - Augusta]

UNION ALL SELECT *
FROM [GLAFS - Bham]

UNION ALL SELECT *
FROM [GLAFS - Adv Med]

UNION ALL SELECT *
FROM [GLAFS - Baywalk]

UNION ALL SELECT *
FROM [GLAFS - Bensonhurst]

UNION ALL SELECT *
FROM [GLAFS - Brandon]

UNION ALL SELECT *
FROM [GLAFS - Chalmette]

UNION ALL SELECT *
FROM [GLAFS - Cheektowaga]

UNION ALL SELECT *
FROM [GLAFS - CMI North]

UNION ALL SELECT *
FROM [GLAFS - CMI South]

UNION ALL SELECT *
FROM [GLAFS - CMI Delray]

UNION ALL SELECT *
FROM [GLAFS - CROV]

UNION ALL SELECT *
FROM [GLAFS - Daytona]

UNION ALL SELECT *
FROM [GLAFS - Dayton]

UNION ALL SELECT *
FROM [GLAFS - Hauppauge MRI]

UNION ALL SELECT *
FROM [GLAFS - Helen Ellis]

UNION ALL SELECT *
FROM [GLAFS - Highway]

UNION ALL SELECT *
FROM [GLAFS - Haup PET]

UNION ALL SELECT *
FROM [GLAFS - MICA]

UNION ALL SELECT *
FROM [GLAFS - Jefferson]

UNION ALL SELECT *
FROM [GLAFS - Jupiter]

UNION ALL SELECT *
FROM [GLAFS - Kaley]

UNION ALL SELECT *
FROM [GLAFS - Long Beach]

UNION ALL SELECT *
FROM [GLAFS - Long Island];

Here is the code for Query 2:

SELECT *
FROM [GLAFS - MD MED]

UNION ALL SELECT *
FROM [GLAFS - Marlin]

UNION ALL SELECT *
FROM [GLAFS - Niagara]

UNION ALL SELECT *
FROM [GLAFS - Panhandle PET]

UNION ALL SELECT *
FROM [GLAFS - PGDI]

UNION ALL SELECT *
FROM [GLAFS - PGDA]

UNION ALL SELECT *
FROM [GLAFS - PG PET]

UNION ALL SELECT *
FROM [GLAFS - East Meadow]

UNION ALL SELECT *
FROM [GLAFS - PILC]

UNION ALL SELECT *
FROM [GLAFS - Pinellas]

UNION ALL SELECT *
FROM [GLAFS - Pittsburgh]

UNION ALL SELECT *
FROM [GLAFS - Park South]

UNION ALL SELECT *
FROM [GLAFS - PLC]

UNION ALL SELECT *
FROM [GLAFS - PMI]

UNION ALL SELECT *
FROM [GLAFS - PTC]

UNION ALL SELECT *
FROM [GLAFS - Queens]

UNION ALL SELECT *
FROM [GLAFS - Rockledge]

UNION ALL SELECT *
FROM [GLAFS - South Coast]

UNION ALL SELECT *
FROM [GLAFS - Stonehill]

UNION ALL SELECT *
FROM [GLAFS - Toledo];

And here is my final query:

SELECT *
FROM [1]

UNION ALL SELECT *
FROM [2];