Calculate YTD mileage when vehicles leave.

[Voyager48]

I am trying to create a query and a report that will produce a current month miles driven and YTD miles driven for a division. The problem is how do I calculate YTD miles driven when a vehicle is sold but I want to count the miles driven in the division's YTD total. I'm creating monthly total using two joined tables one contain Vehicle info including retirement date and division, the other table has the read date (always the first)and odometer reading. To get the current month I am selecting the two month using a two parameter queries then subtracting in third query which I then report from. When a vehicle is sold or transfered the next month odometer is then "0" and this creates a negative useage. I have looked everywhere to for a solution.
I would greatly appreciate any help that anyone can give.

I must say this is the most helpful site I visited everyone does a great job of exchanging information and helping us newbees out. Thank you all!!

 

[JerryDennison]

How does a 0 value yield negative results? What is your formula?

 

[Voyager48]

The Formula is Present Month Odometer reading minus previous month Odometer reading.

 

[JerryDennison]

Ah, ok. What you really need then is a flag to tell you the vehicle is gone and use the highest recorded odometer reading for continued calculations.

 

[Voyager48]

Yes, I think a flag on the retired truck would work. The truck records do have a retirement date available.

Would this require storing the last miles driven number or could I just have the program go back one month until a positive result is obtained?

 

[JerryDennison]

All you need do is find the Max() value for retired vehicles and use it.