creating a query using 3 other queries

[lieip]

I'm trying to create a query that gets the sum of 3 fields and these 3 fields are from 3 other queries.. but when i do that i get an error stating that the query is too complex... how can i fix this problem?

 

[BNPMike]

It is not obvious from what you have said why Access is complaining. Can you show us the query? mike.stephens@bnpparibas.com

 

[lieip]

This is the SQL statement from microsoft access in the sql view:

SELECT qryBurdenDollars.DeptName, qryBurdenDollars.TypeOfChange, qryBurdenDollars.LaborID, [profit]+[ganda]+[burdenDollars] AS totalLabor
FROM qryProfit, qryBurdenDollars INNER JOIN qryGandA ON (qryBurdenDollars.LaborID = qryGandA.LaborID) AND (qryBurdenDollars.TypeOfChange = qryGandA.TypeOfChange) AND (qryBurdenDollars.DeptName = qryGandA.DeptName);

 

[tlbroadbent]

What is the relationship between qryProfit and the other queries? Access may consider the query too complex because it can't determine that relationship. You should also include the table names or an alias on the columns used in the calculation. This reduces complexity, also.

Another problem can be the size of the query string itself. In this case, the query utilizes three other queries and all must be loaded into memory at the same time. The total length of all those queries may exceed an Access limitation. You can reduce the size of the queries by shortening names and/or using aliases.

Suggestions: Use aliases and add JOIN statement for qryProfit.

SELECT
b.DeptName,
b.TypeOfChange,
b.LaborID,
p.profit + g.ganda + b.burdenDollars AS TotalLabor

FROM (qryProfit As p
INNER JOIN qryBurdenDollars As b
ON p.columnX=b.columnY)
INNER JOIN qryGandA AS g
ON b.LaborID = g.LaborID
AND b.TypeOfChange = g.TypeOfChange
AND b.DeptName = g.DeptName;

Hope this helps. If not, check your other post. I posted a link to an article that discusses ways to fix the "query is too complex" problem. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tlbroadbent]

I just noticed that the query you posted in thread181-143404 is different from the query posted in this thread. It is very confusing to have the same question cross-posted in more than one thread. It is even more confusing when the posts differ. Help us out and stick to one thread, please.

With the additional info from the other thread, I have revised the query as follows.
[tt]
SELECT
b.DeptName,
b.TypeOfChange,
b.LaborID,
p.profit + g.ganda + b.burdenDollars AS TotalLabor
FROM (qryBurdenDollars As b
INNER JOIN qryGandA AS g
ON b.LaborID = g.LaborID
AND b.TypeOfChange = g.TypeOfChange
AND b.DeptName = g.DeptName)
INNER JOIN qryProfit As p
ON g.LaborID = p.LaborID
AND g.TypeOfChange = p.TypeOfChange
AND g.DeptName = p.DeptName;[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.