SELECT ContactName, SUM(SumAmount)
FROM
(
SELECT tblContacts.ContactName, Sum(tblInvoices.InvoiceAmount) AS
SumAmount
FROM tblContacts RIGHT JOIN tblInvoices ON tblContacts.ContactID =
tblInvoices.ContactID
GROUP BY tblContacts.ContactName
UNION ALL SELECT tblContacts.ContactName,
Sum(tblPayments.PaymentAmount) AS SumAmount
FROM tblContacts RIGHT JOIN tblPayments ON tblContacts.ContactID =
tblPayments.ContactID
GROUP BY tblContacts.ContactName
)
GROUP BY ContactName

HTH, jens Suessmeyer.

Working with three tables: Contacts, Invoices, Payments. Want a query to
return two columns: Contact Name and Net Amount Due (netting both the
invoice amounts and the payment amounts into one number).

The following Union query gets close, but it still returns two rows for each
contact, one the invoice amount, the other the payment amount:

SELECT tblContacts.ContactName, Sum(tblInvoices.InvoiceAmount) AS
SumOfInvoiceAmount
FROM tblContacts RIGHT JOIN tblInvoices ON tblContacts.ContactID =
tblInvoices.ContactID
GROUP BY tblContacts.ContactName
UNION ALL SELECT tblContacts.ContactName, Sum(tblPayments.PaymentAmount)
FROM tblContacts RIGHT JOIN tblPayments ON tblContacts.ContactID =
tblPayments.ContactID
GROUP BY tblContacts.ContactName

Any suggestions appreciated!

Dan

That works! Thanks!

[quoted text, click to view]