Using 3 tables, with subqueries and sums

[mpemberton]

Here's the setup:

Table 1: accounts
AccountID
name
etc...

Table 2: lineitems
AccountID
code
AmtDue

Table 3: payments
AccountID
PmtAmt

Note: Payments are made to account, not to line items.

My dilemma:
Find all accounts with a (sum of lineitems.AmtDue - sum of payments.PmtAmt) > 0

I've tried several ways, but cannot get it to work.
I'm using Access 2000, and would like it compatible with SQL 7 or SQL 2000.

Thank you very much for your help!
Mark Pemberton
mpemberton@iliant.com

 

[rac2]

What happens when you run this query?

SELECT a.AccountID, SUM(l.AmtDue), SUM(p.PmtAmt)

FROM accounts a,
LEFT JOIN lineitems l ON a.AccountID = a.AccountID
LEFT JOIN payments p ON a.AccountID = p.AccountID

GROUP BY a.AccountID

HAVING ( SUM(l.AmtDue) - SUM(p.PmtAmt) ) > 0

 

[tlbroadbent]

Rac2,

The payment will be multiplied by the number of line items.

Try this instead. It should work in Access and SQL Server.

SELECT a.AccountID, l.AmtDueSum, p.PmtAmtSum
FROM ((accounts a
LEFT JOIN
(Select AccountID, Sum(AmtDue) AS AmtDueSum
From lineitems
Group By AccountID) As l
ON a.AccountID = l.AccountID)
LEFT JOIN
(Select AccountID, Sum(PmtDue) AS PmtAmtSum
From payments
Group By AccountID) As p
ON a.AccountID = p.AccountID)
WHERE l.AmtDueSum <> p.PmtAmtSum Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[mpemberton]

Thanks Terry, that is 99% exactly what I needed. I really appreciate your help. However, I found a problem when there are no payment records on an account that has lineitem records. It doesn't return anything for that specific account. I tried this in both SQL server and Access queries.

Here is my full query (basically what you said, but with a couple updates:

SELECT a.AccountID, l.AmtDueSum, p.PmtAmtSum
FROM ((accounts a
LEFT JOIN
(Select AccountID, Sum(AmountDue) AS AmtDueSum
From lineitems
Group By AccountID) As l
ON a.AccountID = l.AccountID)
LEFT JOIN
(Select AccountID, Sum(PaymentAmt) AS PmtAmtSum
From payments
Group By AccountID) As p
ON a.AccountID = p.AccountID)
WHERE l.AmtDueSum+p.PmtAmtSum > 0

I've struggled with this so much (and about to give up), it may be a quick fix, so whatever you have to offer, I would greatly appreciate it.

Thanks,
Mark

 

[tlbroadbent]

Sorry, I didn't think clearly before posting the solution. The problem is in the where clause. Use the NZ function in Jet SQL to handle NULL values. Use the Isnull function on SQL Server.

SELECT a.AccountID, l.AmtDueSum, p.PmtAmtSum
FROM ((accounts a
LEFT JOIN
(Select AccountID, Sum(AmountDue) AS AmtDueSum
From lineitems
Group By AccountID) As l
ON a.AccountID = l.AccountID)
LEFT JOIN
(Select AccountID, Sum(PaymentAmt) AS PmtAmtSum
From payments
Group By AccountID) As p
ON a.AccountID = p.AccountID)
WHERE (nz(l.AmtDueSum,0) - nz(p.PmtAmtSum,0)) > 0 Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[mpemberton]

Thanks Terry, that is perfect. I had used the nz function before, but was brain dead with this one. Thank you for your help.