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:
Code:
SELECT tmp.InvoiceID, SUM(tmp.TotalDue) AS BalanceDue
FROM [
SELECT InvoiceDetails.InvoiceID, Sum(InvoiceDetails.Qty*InvoiceDetails.Price) AS TotalDue
FROM InvoiceDetails
GROUP BY InvoiceDetails.InvoiceID
UNION
SELECT CustPayments.InvoiceID, -1*Sum(CustPayments.Payment) AS TotalDue
FROM CustPayments
GROUP BY CustPayments.InvoiceID
]. AS tmp
GROUP BY tmp.InvoiceID;
hth
Phil