Query for largest payment

[Jackxxx]

I need to get the largest payment and date of the payment for all clients. My table has clientID, AmountPaid, and PayDate fields. I have tried using max() on the amountpaid but it does not work, the max date might not be the date the largest payment was made.

Any help is greatly appreciated.

Thank you,

 

[Jackxxx]

Also, I need to only return one for each client, they might have made payments of the same amount.

 

[PHV]

A starting point (SQL code)

SELECT A.clientID, A.AmountPaid, Max(A.PayDate) AS LastMaxPayDate
FROM [My table] A INNER JOIN (
SELECT clientID, Max(AmountPaid) AS Largest FROM [My table] GROUP BY clientID
) B ON A.clientID = B.clientID AND A.AmountPaid = B.Largest
GROUP BY A.clientID, A.AmountPaid

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Jackxxx]

I think this could work, but I just had another piece added that I have no Idea how to add to your statement.

I also need to include the Program the payment was posted to. I tried just adding the ProgramType field, but if they made multiple payments for the same amount to different programs then I get a row for each.

What can I do to your sample to get just one row returned?

Thank you,