Help filtering a report

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have 2 tables:

AUTOS<Oil Changes

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!!!

Photo of Amanda


  • 10 Points

Posted 3 years ago

  • 0
  • 1
I suggest that you do Summary Maximum of the Next Oil Change Milage. call it [Next Oil Change Due].

Then simply filter your autos report where [Current Mileage] <= [Next Oil change due]