calculating a value based on a previous entry

[mmasuda]

I am trying to calculate the difference between to values either in the query or on the form.
The data is as follows:

Id    Odometer Reading  Miles Driven 
1      3081               ?
2      3481               ? 
3      3751               ?

This data is part of a table which relates to a vehicle table. So there is a record for each vehicle and the gasoline consumption.

How can I subtract the Odometer readings from each other to calculate the actual miles driven?

Thanks in advance,
MM

 

[Crowley16]

I'd use a subquery to get the previous Odometer for each value and subtract it, however this is a bit ineffecient so if you have lots of data, you could try joining the table with itself, on ID = ID - 1, although this assumes you've got a set incremental id with a standard interval and no breaks...

--------------------
Procrastinate Now!

 

[mmasuda]

I tried using the self join and also a subquery. Both resulted in giving me a colum with the id of one less, however the odometer reading was the same