Group By Question

[Nick McCamy]

I have a table called InvoiceDetails that has the
following fields:

InvoiceID
ProductID
Qty (quantity bought)
Price (product price)

I have a second table called CustPayments with the
following fields:

InvoiceID
Date
Payment

What I'm trying to do in one query is to obtain the
balance due for each InvoiceID. So far I have the
following:

SELECT InvoiceDetails.InvoiceID, Sum([Price]*[Qty]) AS
[Total Due], CustPayments.Payment
FROM InvoiceDetails
INNER JOIN CustPayments ON InvoiceDetails.InvoiceID =
CustPayments.InvoiceID
GROUP BY InvoiceDetails.InvoiceID, CustPayments.Payment;

How do I subtract the payments made from the total due to
get the balance due in this Group By query?

This is what is I want to happen, step-by-step:

1. Sum the (Price * Qty) for each
InvoiceDetails.InvoiceID
2. Sum the Payment for each CustPayments.InvoiceID
3. Subtract step 2 from step 1 for each InvoiceID to get
the balance due

Here's a very simple example of what I'm trying to do:

Here is the InvoiceDetails table:

InvoiceID ProductID Qty Price
1 4 1 $20.00
1 1 3 $10.00
1 6 1 $50.00
2 2 1 $60.00

Customer1 (InvoiceID = 1) has purchased six items:

1 @ $20
3 @ $10
1 @ $50

for a total due of $100.

Customer2 (InvoiceID = 2) purchased 1 item for $60.

Here is the CustPayments table:

InvoiceID Date Payment
1 1/4/2004 $30.00
1 1/10/2004 $70.00
2 2/10/2004 $20.00
2 2/20/2004 $5.00

Customer1 made two payments totaling $100, so their
balance due is $0.
Customer2 made two payments totaling $25, so their
balance due is $60 - 25 = $35.

How do I get the balance due for each InvoiceID in a
single query?
Perhaps there's a better way to create the tables?

 

[John Viescas]

You cannot do it by joining InvoiceDetails to CustPayments - the resulting
number of rows will be the Cartesian product of the two tables on InvoiceID.
For example, InvoiceID 1 will return 6 rows - the three from InvoiceDetails
times the two from CustPayments. You can do it with three separate queries:

qryInvTot:
SELECT InvoiceID, Sum([Price]*[Qty]) AS [Total Due]
FROM InvoiceDetails
GROUP BY InvoiceID

qryPayTot:
SELECT InvoiceID, Sum([Payment]) As [Total Paid]
FROM CustPayments
GROUP BY InvoiceID

qryResult:
SELECT qryInvTot.InvoiceID, qryInvTot.[Total Due], NZ(qryPayTot.[Total
Paid], 0)
FROM qryInvTot LEFT JOIN qryPayTot
ON qryInvTot.InvoiceID = qryPayTot.InvoiceID

I used an outer join in the final query so that you'll also see invoices
that don't have any payments yet.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I have a table called InvoiceDetails that has the
following fields:

InvoiceID
ProductID
Qty (quantity bought)
Price (product price)

I have a second table called CustPayments with the
following fields:

InvoiceID
Date
Payment

What I'm trying to do in one query is to obtain the
balance due for each InvoiceID. So far I have the
following:

SELECT InvoiceDetails.InvoiceID, Sum([Price]*[Qty]) AS
[Total Due], CustPayments.Payment
FROM InvoiceDetails
INNER JOIN CustPayments ON InvoiceDetails.InvoiceID =
CustPayments.InvoiceID
GROUP BY InvoiceDetails.InvoiceID, CustPayments.Payment;

How do I subtract the payments made from the total due to
get the balance due in this Group By query?

This is what is I want to happen, step-by-step:

1. Sum the (Price * Qty) for each
InvoiceDetails.InvoiceID
2. Sum the Payment for each CustPayments.InvoiceID
3. Subtract step 2 from step 1 for each InvoiceID to get
the balance due

Here's a very simple example of what I'm trying to do:

Here is the InvoiceDetails table:

InvoiceID ProductID Qty Price
1 4 1 $20.00
1 1 3 $10.00
1 6 1 $50.00
2 2 1 $60.00

Customer1 (InvoiceID = 1) has purchased six items:

1 @ $20
3 @ $10
1 @ $50

for a total due of $100.

Customer2 (InvoiceID = 2) purchased 1 item for $60.

Here is the CustPayments table:

InvoiceID Date Payment
1 1/4/2004 $30.00
1 1/10/2004 $70.00
2 2/10/2004 $20.00
2 2/20/2004 $5.00

Customer1 made two payments totaling $100, so their
balance due is $0.
Customer2 made two payments totaling $25, so their
balance due is $60 - 25 = $35.

How do I get the balance due for each InvoiceID in a
single query?
Perhaps there's a better way to create the tables?