Cant get query to total correctly

[Steve]

I have the following sql:

SELECT tblPayments.InvoiceId, tblPayments.PaymentId,
tblPayments.PaymentDate, tblPayments.PaymentCheckNumber,
Sum(tblPayments.PaymentAmount) AS SumOfPaymentAmount
FROM tblPayments
GROUP BY tblPayments.InvoiceId, tblPayments.PaymentId,
tblPayments.PaymentDate, tblPayments.PaymentCheckNumber;

There are six rows in the table. I need a total by InvoiceId which would
give me a total of 4 rows, 2 invoices having 2 payments each.

I know I am missing something simple, but just am not seeing it.

TIA

 

[Allen Browne]

Presumably PaymentID is the primary key of tblPayments?

You have this field in the GROUP BY clause.
Consequenty, your query will give you a row for every payment.

Omit the PaymentID and PaymentDate field from the query.
It should then show you the correct total payment amount per invoice.

 

[Steve]

Worked fine after I got it down to the InvoiceId and PaymentAmount fields.

Thanks for the help

Steve