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

I assume you have a many-to-many link between InvoiceDetails and CustPayments? If so you won't get very far joining these 2 tables because that will result in some values being counted multiple times. For example if a certain invoice has 3 product records and 2 payment records you'll end up with 6 records in the joined table, which means your "Due" will be counted twice and your "Paid" will be counted 3 times.

Try something like this nasty-looking bit of SQL:
hth
Phil