Cant get query to total correctly

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
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.
 
Worked fine after I got it down to the InvoiceId and PaymentAmount fields.

Thanks for the help

Steve
 
Back
Top