Select only most recent payment

[Helen]

I'm using ACCESS 2003. I'm trying to run a query to list a contributor's
most recent payment of multiple payments per contributor. Following is the
query that I used:

SELECT DISTINCTROW Contributor.NamePart1, Contributor.NamePart2,
Contributor.Status, Payment.PaymentDate, Payment.Amount
FROM Contributor INNER JOIN Payment ON Contributor.ContributorID =
Payment.ContributorID
WHERE (((Contributor.Status)<>"Inactive"))
GROUP BY Contributor.NamePart1, Contributor.NamePart2, Contributor.Status,
Payment.PaymentDate, Payment.Amount
HAVING (([Payment].[PaymentDate]=Max([Payment].[PaymentDate])))

The query runs, but isn't selective. It lists all payments for each
contributor, not just the most recent one.

I would very much appreciate any help!

Helen

 

[Tom Wickerath]

Hi Helen,

I believe you will need a subquery to solve this problem. Try Access MVP
Allen Browne's tutorials on subqueries:

Subquery basics
http://allenbrowne.com/subquery-01.html

Surviving Subqueries
http://allenbrowne.com/subquery-02.html



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
_______________________________________________


I'm using ACCESS 2003. I'm trying to run a query to list a contributor's
most recent payment of multiple payments per contributor. Following is the
query that I used:

SELECT DISTINCTROW Contributor.NamePart1, Contributor.NamePart2,
Contributor.Status, Payment.PaymentDate, Payment.Amount
FROM Contributor INNER JOIN Payment ON Contributor.ContributorID =
Payment.ContributorID
WHERE (((Contributor.Status)<>"Inactive"))
GROUP BY Contributor.NamePart1, Contributor.NamePart2, Contributor.Status,
Payment.PaymentDate, Payment.Amount
HAVING (([Payment].[PaymentDate]=Max([Payment].[PaymentDate])))

The query runs, but isn't selective. It lists all payments for each
contributor, not just the most recent one.

I would very much appreciate any help!

Helen

 

[Helen]

Thanks, Tom.

The subquery worked.

Helen