Finding Previous Maintenance Date from a former Record (1 Viewer)

machinetech

New member
Local time
Today, 10:46
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:46
Joined
Oct 29, 2018
Messages
21,358
Hi. You can't use the job number but you can use the MaintenanceDate with DMax().
 

Micron

AWF VIP
Local time
Today, 11:46
Joined
Oct 20, 2018
Messages
3,476
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.
 

machinetech

New member
Local time
Today, 10:46
Joined
Jun 10, 2019
Messages
7
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?
 

isladogs

MVP / VIP
Local time
Today, 15:46
Joined
Jan 14, 2017
Messages
18,186
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
 

machinetech

New member
Local time
Today, 10:46
Joined
Jun 10, 2019
Messages
7
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

Top Bottom