how to get value from prev. record in a calculation?

[Guest]

I'm tracking vehicle information, including mileage and gallons of gas used.
I want to calculate miles per gallon (MPG).
How can I get/use the mileage value from the previous record to use in a
calculation in the current record to get MPG?

 

[Michel Walsh]

Hi,


SELECT a.carID, a.DateTimeStamp,b.DateTimeStamp,LAST(a.gallon) / (
LAST(b.mileage) -LAST(a.mileage))
FROM (myTable As a INNER JOIN myTable As b
ON a.carID=b.carID AND a.DateTimeStamp <
b.DateTimeStamp)
INNER JOIN myTable As c
ON a.carID=c.carID AND a.DateTimeStamp < c.DateTimeStamp
GROUP BY a.carID, a.DateTimeStamp,b.DateTimeStamp
HAVING b.DateTimeStamp =MIN(c.DateTimeStamp)



where I assume your table is with fields like:

CarID, DateTimeStamp, Milleage, Gallon



Hoping it may help,
Vanderghast, Access MVP