Need a list of last payment for each client 1

[Jackxxx]

Clients make payments on loans, they are inserted into tblPayments. I need to return a list of the LAST PAYMENT made by each client. I need to see clientID, PaymentDate, and PaymentAmount.

client1 2/2/2007 $100 < this is the last payment they made, they may have made other payments but this was the last.

client2 3/2/2007 $50 < this is the last payment they made, they may have made other payments but this was the last.

client3 4/1/2007 $1000 < thi si the last payment they made, they may have made other payments but this was the last.

etc...

Thank you

Jackxxx

 

[SQLBill]

Can you provide us your schema, sample data, the results you want to see, AND what you have done so far?

-SQLBill

Posting advice: FAQ481-4875

 

[Jackxxx]

It is simple, tblPayments has three fields I need clientid, paymentdate, and paymentamount.
I need to query a list that returns the last payment made by each client.

Select dbo.tblPayments.ClientID, dbo.tblPayments.PaymentDate, dbo.tblPayments.PaymentAmount FROM dbo.tblPayments WHERE dbo.tblPayments.PaymentDate = (SELECT MAX(dbo.tblPayments.PaymentDate) FROM dbo.tblPayments)
GROUP BY dbo.tblPayments.ClientID, dbo.tblPayments.PaymentDate, dbo.tblPayments.PaymentAmount

The problem here is it only returns the last payment entered. I need a list of the last payment made by each client.

 

[gmmastros]

[COLOR=blue]Select[/color] dbo.tblPayments.ClientID, 
       dbo.tblPayments.PaymentDate, 
       dbo.tblPayments.PaymentAmount 
[COLOR=blue]FROM[/color]   dbo.tblPayments 
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] dbo.tblPayments.ClientID, 
                [COLOR=#FF00FF]Max[/color](dbo.tblPayments.PaymentDate) [COLOR=blue]As[/color] LastPayment
         [COLOR=blue]FROM[/color]   dbo.tblPayments 
         [COLOR=blue]Group[/color] [COLOR=blue]By[/color] dbo.tblPayments.ClientID
         ) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] dbo.tblPayments.ClientId = A.ClientId
         And dbo.tblPayments.PaymentDate = A.PaymentDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom