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



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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question