Comparing a value from two different records in an Access Report

[and13345]

I have a report that has mileage tracking information in it. I have several fields, one being MT_Odometer_Start and another being MT_Odometer_Finish. What I want to do is compare the MT_Odometer_Start from one record to the MT_Odometer_Finish in another record (the previous record). For instance on 2/2/10 the MT_Odometer_Start value is 22000 and the MT_Odometer_Finish value is 22150, that is one record, the next record on 2/3/10 has MT_Odometer_Start of 22170 and MT_Odometer_Finish of 22200. What I want to do is compare the falue of MT_Odometer_Start in the 2/3/10 record to the MT_Odometer_Finish in the 2/2/10 record. I can't figure out how to make the MT_Odometer_Finish value from the previous record available to the current record. If the value of MT_Odometer_Start in the current record is greater than the value of MT_Odometer_Finish in the previous record I want to display MT_Odometer_Start - MT_Odometer_Finish in the report

I am using Access 2007.

[pbaldy]

One way:

http://www.baldyweb.com/PrevValue.htm

[and13345]

Won't that query have a bit of a flaw if there are more than one record for the same vehicle on the same date? In my case I may have two or three entries for the same vehicle on the same day. Say one with ending miles of 22100, one with 22199 and one with 22290. How do I guarantee that the previous one is the right one? I guess if I sort using the VehicleID, then Date then Ending odometer, that should put them in the correct order?

[pbaldy]

Yes, that should work.

[and13345]

Ok, I was able to modify your query to match my fields and it works, until I have multiple entries for the same vehicle on the same date. I can even get the query in your datebase to produce the same error if I add a second record on the same date for the same carnum.

The error is this "At most one record can be returned by this subquery."

If you add the following record to your database you will see the same thing.

VehicleMiles
DorDate 5/3/2008
CarNum 17
SpeedoEnd 201800