Limiting query results to record with most recent date

Question

I am trying to run an access query that shows me the overdue rental agreements in the table Rental_Agreements. There are multiple rental agreements for each locker in the Bike_Locker table, so I tried to use the MAX function on the date for the end of the rental period (Period_End field). However, I'm still getting multiple records for some lockers.

Here is my SQL code. Any advice is much appreciated.

SELECT Rental_Agreements.Renter_ID, Rental_Agreements.Locker_ID, Max(Rental_Agreements.Period_End) AS Exp_Date
FROM Rental_Agreements
GROUP BY Rental_Agreements.Renter_ID, Rental_Agreements.Locker_ID;

Answer 1

I think you want your code to show which Renter_ID has the highest (most recent) Period_End. For this, you need to first find the most recent period end for each locker, then match that against the records to find the relevant renter.

SELECT
    Rental_Agreements.Locker_ID,
    Rental_Agreements.Renter_ID
FROM
    Rental_Agreements
WHERE
    Rental_Agreements.Period_End = (
        SELECT 
            MAX(RA.Period_End) 
        FROM 
            Rental_Agreements RA 
        WHERE 
            RA.Locker_ID = Rental_Agreements.Locker_ID
        )