Finding Previous Maintenance Date from a former Record

machinetech

New member
Local time
Yesterday, 20:27
Joined
Jun 10, 2019
Messages
7
My company is keeping track of the maintenance on five of our machines.
I have a form to input the machine the maintenance was performed on, the date it was performed, and the severity of the maintenance, and job # as the primary id.
I then have a query that shows the maintenance record for just 1 of the five machines.
I would like to add a field in the query that calculates the number of days since the last maintenance was performed. To do this I need the value for the most recent maintenance date. I can not use the dlookup since the job # id is not in sequential order. Any help on this would be great.
Please let me know if I need to explain further.

JOB# Machine# MaintenanceDate Grade
1 1 3/1/18 A
2 3 3/1/18 B
3 1 4/20/18 A
4 2 5/1/18 C

What I want:

JOB# MACHINE# DATE GRADE PREVDATE #DAYS
1 1 3/1/18 A
3 1 4/20/18 A 3/1/18 50
 
Hi. You can't use the job number but you can use the MaintenanceDate with DMax().
 
the number of days since the last maintenance was performed.
That is different from the data you posted, which is the difference between the last 2 dates, not the difference from today and the last date.
If you use DMax I think the query would only be useful when the current maintenance has no date. In other words, if maintenance date is today, you'll get last date if you run it today, which should be OK, but tomorrow you'll get today's date. Is that OK, or would you want today's date and the prior date? Or between the last 2 as you've shown?

To calculate from the last 2 dates usually involves a subquery.
 
between the last 2 as you've shown?

This is what I would need. I figured it would need a sub query. I just haven't figured out what the code would be for it. Could you help with this?
 
machinetech
Glad to hear you solved your issue.
However. threads with replies should not be deleted as they may be useful to others.
Following a comment from another forum member I have restored the thread and marked it solved
 
Sorry I did not respond. But i decided to go a different route with what I was looking for.

Thanks for the help though.
 

Users who are viewing this thread

Back
Top Bottom