Query that returns all record fields based on one field

[Ralph]

Hi All,

Never posted before, but have dived into MS Access and am getting there.... slowly.... I think. Apologies in advance if this is a really daft question, but -

I have a table that has the following fields: Payment ID; Payee Name; Payment Date; Payment Amount

I have a query that I want to return "the most recent payment for each Payee" along with the relevant amount. As it stands, my query returns "the most recent payment for each payee for each payment amount" (ie three or four different payments as the payee has paid different amounts each time).

Does this make any sense at all?

Ralph

 

[CJ_London]

try:

SELECT [Payment ID], [Payee Name], [Payment Date], [Payment Amount] FROM YourTable WHERE [Payment Date] = (SELECT MAX([Payment Date] From YourTable AS Tmp WHERE [Payee Name]=YourTable.[Payee Name])

You'll need to substitute table and field names for what you actually have

PS Lose those spaces then you can lose the square brackets

 

[Ralph]

Thank you!

I have used this, but it seems I'm missing ),],or Item in query expression.....

 

[pr2-eugin]

A closing parentheses at the end of Max() in the Subquery, should fix it..

 

[CJ_London]

Oops! Paul is right

 

[Ralph]

Brilliant - Works like a charm!

Thanks guys.... (which undoubtedly means I'll be back).