How do I find the first previous date from the last date?

[Hamboneman]

I am working on a Preventive Maintenance Database and want to compare the last date of PM to the previous date of PM from a table with completed PM data. I can query to get the date of the last PM, but how do I query to get the date of first PM previous to the last?

 

[mdthornton]

Write a query, qPMLast say, to return your latest pm date:

select max(pmdate) as lastPMDate
from pmdata

Then

select max(pmdata.pmdate) as secondlastPMDate
from pmdata, qPMLast
where pmdate < qPMLast.lastPMDate Best Regards,
Mike

 

[Hamboneman]

I have a query of a table of completed Preventive Maintenance with dates, serial #'s and hour meter readings. I group that query using group by last date and end up with the most recent PM date for each serial number. I need to query for the next to last PM, so I can subtract the next to last date from the last date, so I can compare that to what we were supposed to do by number of days.

 

[braindead2]

mdthornton's solution should get you what you want. Another option would be to use dmax()=(speed killer)
dmax([pmdate],&quot;tablename&quot;,&quot;serialnumer = 123 and date < query.maxdate)