Getting Previous Record to Calculate Change from current record

[markfmcc]

I am setting up a database to track my fuel useage versus miles driven.

I have a fuel transactions table that records the transaction date, vehicle number, odometer reading, and gallons pumped.

eg
TrxDate VehNo Odometer Gallons Pumped
1/11/05 222 15000 5
2/11/05 222 15060 10
3/11/05 222 15090 10

I need to crate a report that gives me miles per gallon for each transaction.

In order to get this I need to be able to subtract the previous records odometer reading from the current records odometer reading. This will give me the miles travelled between readings.

My question is: How do I get the system to pick up the previous reading for a specific vehicle from the fuel transactions table?

Please help.
Thanks in advance,
Mark

 

[rac2]

It can be done, but as the table grows, the query will bog down due to the need to find the previous row for every row.

I think it is better to have the beginning and ending odometer readings in the same row in such a table. When you record the fillup, add a row using the maximum previous ending reading as the value for the beginning reading like so-

INSERT FuelUsage ( vin, odo_start, odo_end, gallons )
SELECT [Vehicle Number],
       MAX(odo_end),
       [Odometer Reading],
       [Gallons Pumped]
FROM FuelUsage
WHERE vin = [Vehicle Number]

This way you find the previous odometer reading one time, when the data is recorded, instead of every time you get the report.

Since the mpg is know at this time, I would calculate and store it when the data is recorded rather than using a computed column.