Run a query that gives last entry prior to date

[crimedog]

there are multiple reps that can have quota changes throughout the year - I need a query that returns the latest quota change prior to a specific date
I have attached a Database - See QuryQuota1
There should be 3 results Rep 1 Quota - Rep 2 Quota - Rep 3 quota



It is returning 4 results (2 quotas for rep 1 ) because I am asking for the last quota change before or equal to January

[June7]

Maybe TOP N will accomplish. Review http://allenbrowne.com/subquery-01.html#TopN

[rpeare]

create this query

SELECT TblQuota.RepID, Max(TblQuota.DateOfChange) AS MRChange
FROM TblQuota
GROUP BY TblQuota.RepID;

Save it as qryMRChange


Create this query

SELECT TblQuota.ID, qryMRChange.RepID, qryMRChange.MRChange, TblQuota.Quota
FROM qryMRChange LEFT JOIN TblQuota ON (qryMRChange.MRChange = TblQuota.DateOfChange) AND (qryMRChange.RepID = TblQuota.RepID)
GROUP BY TblQuota.ID, qryMRChange.RepID, qryMRChange.MRChange, TblQuota.Quota;

Should give you the most recent quota record for your reps

This is making use of an AGGREGATE query to find the most recent date (this assumes there is 1 and only 1 record per rep per day) then links that back to the quota table to get the specific information (quota volume)