Trouble with a query

[schongresh]

I am designing a database that will produce reports on the fuel usage
and mileage of fleet vehicles. The main table is the
VehicleFuelLog;the fields are: [fuelID]-[fuelTruck]-[fuelDate/Time]-
[fuelMileage]-[fuelGallons]. In order to get the mileage for each day,
I need to get the mileage at the time of fueling and the previous
mileage or starting miles. I was using a dlookup to get the mileage
from the last record, but with 20 trucks in the fleet the ID numbers
are not sequential
( DLookUp("[fuelMileage]","VehicleFuelLog","[fuelID=]"&[fuelID]-1).
What I need the query to return is [fuelTruck],[fuelDate/Time],
[fuelMileage],[exp1: whatever will get me the mileage from the last
time this particular truck was logged]--I thought of adding a new
autoincrement to the query; the records would be sorted ascending by
date then given sequential numbers, but I do not know how to do that.
There is probably a very easy way to get where I want to go-- any help
would be appreciated. I am new to building dbase's so include as much
detail as you can possibly stand.

Thanks

 

[Duane Hookom]

Try something like:
SELECT [VehicleFuelLog].*,
([fuelMileage]-(SELECT Top 1 FuelMileage
FROM VehicleFuelLog L
WHERE L.FuelTruck = VehicleFuelLog.FuelTruck
AND L.[FuelDate/Time] < VehicleFuelLog.[FuelDate/Time]
ORDER BY [FuelDate/Time] DESC))/[FuelGallons] As Mileage
FROM VehicleFuelLog