Limitation of UNION Query

[Len Boorman]

Inherited a crap database.

Got 61 tables each with 16 fields. Should be 1 Table with 16 fields.

Tried a UNION ALL query to give me a result that looks like a single table but got Query too complex error. Shaved it down a bit until okay and then created second UNION for the missing bits and then UNIONed the two UNION queries so got there okay in the end.

Question is

What are the limits of a UNION query.

Is it simply the number of fields that limits the number of Select statements
or
The number of Select statements limits the number of fields
or
Something in between

Have solved my immediate problem but would like to know what the limitations of UNION

Len B

 

[RV]

Max number of tables in a query = 32, guess that's your problem...

RV

 

[Len Boorman]

Max number of tables in a query = 32, guess that's your problem...

RV

Thanks for reply.

Think that 32 may apply to other type of query.

My UNION query fell over at 16 fields per table and at 50 tables

i.e nominally 800 fieldtables

Initially gave me a problem querying this query until I reduced it further.



Len B

 

[ShanVel]

Limitation of Union Query

Len,

I have the same problem. I have to pull in 65 records from 66 tables with each record containing 19 fields. Access complains with "query is too complex" if I exceed more than 50 tables x 19 fields each.

Hence, I split it into two unions and tried to do UNION on UNION as you suggested. Same "query is too complex" error. May be I didn't write my UNION on UNION query correctly. Here's is the sample:

SELECT qryRprtRskTblP1.P.intProjectId, qryRprtRskTblP1.chrProjectName, qryRprtRskTblP1.chrBlrPropNum, qryRprtRskTblP1.A1.intProjectId, qryRprtRskTblP1.memGuranItem, qryRprtRskTblP1.memFuel, qryRprtRskTblP1.memOpertgCondn, qryRprtRskTblP1.memPred, qryRprtRskTblP1.memGuar, qryRprtRskTblP1.memMargin, qryRprtRskTblP1.memCustReqst, qryRprtRskTblP1.memActual, qryRprtRskTblP1.memRiskLevel, qryRprtRskTblP1.memLDs, qryRprtRskTblP1.memBasis, qryRprtRskTblP1.memCorrectAction, qryRprtRskTblP1.memComments, qryRprtRskTblP1.chrCategoryID, qryRprtRskTblP1.intCategoryRowID
FROM qryRprtRskTblP1
WHERE qryRprtRskTblP1.P.intProjectId=qryRprtRskTblP1.A1.intProjectId
UNION ALL
SELECT qryRprtRskTblP2.P.intProjectId, qryRprtRskTblP2.chrProjectName, qryRprtRskTblP2.chrBlrPropNum, qryRprtRskTblP2.F5.intProjectId, qryRprtRskTblP2.memGuranItem, qryRprtRskTblP2.memFuel, qryRprtRskTblP2.memOpertgCondn, qryRprtRskTblP2.memPred, qryRprtRskTblP2.memGuar, qryRprtRskTblP2.memMargin, qryRprtRskTblP2.memCustReqst, qryRprtRskTblP2.memActual, qryRprtRskTblP2.memRiskLevel, qryRprtRskTblP2.memLDs, qryRprtRskTblP2.memBasis, qryRprtRskTblP2.memCorrectAction, qryRprtRskTblP2.memComments, qryRprtRskTblP2.chrCategoryID, qryRprtRskTblP2.intCategoryRowID
FROM qryRprtRskTblP2
WHERE qryRprtRskTblP2.P.intProjectId=qryRprtRskTblP2.F5.intProjectId;


Len, could you kindly show me if this is how my combined query should look like?

Anyone has any suggestion would be greatly appreciated. Pardon me to give lots text on coding for your reading, I have wasted all my time to figure out if this is Access problem or I messed up my query.

ShaVel

Inherited a crap database.

Got 61 tables each with 16 fields. Should be 1 Table with 16 fields.

Tried a UNION ALL query to give me a result that looks like a single table but got Query too complex error. Shaved it down a bit until okay and then created second UNION for the missing bits and then UNIONed the two UNION queries so got there okay in the end.

Question is

What are the limits of a UNION query.

Is it simply the number of fields that limits the number of Select statements
or
The number of Select statements limits the number of fields
or
Something in between

Have solved my immediate problem but would like to know what the limitations of UNION

Len B

 

[Pat Hartman]

You can create several union queries. Query1 unions tbl1, tbl2, tbl3. Query2 unions tbl4, tbl5, tbl6. Query3 unions tbl7, tbl8, tbl9. Query4 unions query1, query2, query3.

Figure out how many tables you can union in each initial query.

If you still havt trouble with this, you can create a temp table and simply append rows to it. If you have a lot of data, this might ultimately be a faster method.

 

[ShanVel]

Pat: Thank you for the prompt response. I will try and let you know.

 

[Kowalski]

I find that if you replace UNION ALL with UNION then it will also work.