Return most recent record [Repost]

[Jordan]

I have a table of payments made by clients. It includes
the fields:

PaymentID
ClientID
DateRec
AmtRec

I was trying to use the Last function on the Total row to
return the most recent payment information for each
client. However, I found these are unreliable and using
the Max function was suggested instead. However, this
would only return the most recent date. How could I get
the most recent AmtRec with it? If I put GroupBy for
AmtRec, it'll return each payment separate. Using Max
doesn't help because the most recent payment isn't
necessarily the largest. Is there any other way I can
get the most recent record information for each client?

 

[Jeff Boyce]

Jordan

One approach would be to take two small steps. The first step you already
have -- identify the Max() of the dates. The second step would be to use
the ID plus MaxOfDate query as one "table" in a new query. The second
"table" can be the original table. Join the two on the ID and date fields
and return the Amount.

 

[Guest]

That worked. Thanks.