I have 2 tables:
I need a report that shows which Autos are due for Oil Changes.
Drivers enter their mileage in weekly to a field on the Autos form. It does not create a new record, it just overwrites the information in that field.
In the Service Record Table, I have a report that has filtered the Autos whose Mileage(looked up from Autos table) is greater than a mileage amount ("Next Oil Change Due" field) set the last time they created an oil change(in oil change table).
My problem is that obviously ALL previous oil change records have a "Next oil change due" amount that is less than the most recent Auto Mileage.
I have figured out how to create a Maximum Record id relationship so i can look at only the most recent oil changes... But then I need to filter those to show which are overdue.
I feel like there is a simple solution but I have overthought it so much I am getting further away from the answer!!!