Query that returns all record fields based on one field

Ralph

Registered User.
Local time
Today, 09:32
Joined
Mar 13, 2013
Messages
16
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? :o

Ralph
 
try:

Code:
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
 
Thank you!

I have used this, but it seems I'm missing ),],or Item in query expression.....
 
A closing parentheses at the end of Max() in the Subquery, should fix it..
 
Brilliant - Works like a charm!

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

Users who are viewing this thread

Back
Top Bottom