Aggregate query across four related tables

[deutz]

Hi and thanks in advance,

(Using Access 2003)

I'm trying to create a query that brings together job/payment data from 4 related tables and group by client.

Table 1 tblClient has primary key as fldClientID and also has fldClientName

Table 2 tblJob has primary key as fldJobID and foreign key is fldClientID

Table 3 tblJobItem has dollar amounts for all jobs in fldItemAmt and foreign key is fldJobID

Table 4 tblPayment has dollar amounts paid for all jobs in fldPaymentAmt and foreign key is fldJobID

So I want to display one row for each client with the total amount incurred for all jobs and the total paid for all jobs.

I tried doing a left join between tblClient and tblJob and using two subqueries to get summed values from tblJobItem and tblPayment.

When attempting to run the SQL below I get error msg ... “You tried to execute a query that does not include the specified expression 'fldJobID' as part of the aggregate function.”

Any idea what I am doing wrong here?

SELECT tblClient.fldClientID, tblClient.fldClientName, (SELECT Sum(tblJobItem.fldItemAmt) FROM tblJobItem WHERE tblJobItem.fldJobID = tblJob.fldJobID) AS Total, (SELECT Sum(tblPayment.fldPaymentAmt) FROM tblPayment WHERE tblPayment.fldJobID = tblJob.fldJobID) AS Paid
FROM tblClient LEFT JOIN tblJob ON tblClient.fldClientID=tblJob.fldClientID
GROUP BY tblClient.fldClientID, tblClient.fldClientName

 

[PHV]

Why not simply this ?
SELECT C.fldClientID, C.fldClientName, Sum(I.fldItemAmt) AS Total, Sum(P.fldPaymentAmt) AS Paid
FROM ((tblClient C
LEFT JOIN tblJob J ON C.fldClientID=J.fldClientID)
LEFT JOIN tblJobItem I ON I.fldJobID = J.fldJobID)
LEFT JOIN tblPayment P ON P.fldJobID = J.fldJobID
GROUP BY C.fldClientID, C.fldClientName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[deutz]

Thanks PHV,

I tried your solution but the Job amounts and Payment amounts returned are greater than the sum of what's in the tables so I'm getting inaccurate figures. I think it has something to do with grouping across four tables, creates some kind of cartesian product or something where there is some duplication when summing the groups?

 

[deutz]

Thanks for your suggestion. In the mean time another SQL genius has come up with this solution which seems to produce the desired results ...

SELECT tblClient.fldClientID, tblClient.fldClientName, A.SumOffldItemAmt AS Total, B.SumOffldPaymentAmt AS Paid
FROM (tblClient
LEFT JOIN
(SELECT tblJob.fldClientID, Sum(tblJobItem.fldItemAmt) AS SumOffldItemAmt
FROM tblJob INNER JOIN tblJobItem ON tblJob.fldJobID = tblJobItem.fldJobID
GROUP BY tblJob.fldClientID
) as A ON tblClient.fldClientID = A.fldClientID)
LEFT JOIN (SELECT tblJob.fldClientID, Sum(tblPayment.fldPaymentAmt) AS SumOffldPaymentAmt
FROM tblJob INNER JOIN tblPayment ON tblJob.fldJobID = tblPayment.fldJobID
GROUP BY tblJob.fldClientID
) as B ON tblClient.fldClientID = B.fldClientID;

Regards
deutz