Create a calculation based on field in the previous record

[SusanP]

I'm doing a vehicle report for a fleet of vehicles. The table includes, among
other things, the date, current mileage and gallons of fuels purchased. I
would like to set up a query with a calculated field for miles per gal. This
would entail having the query include the previous record's current mileage,
subtracted from the current record's current mileage to get the mileage
between fueling. Any ideas?

 

[Marshall Barton]

I'm doing a vehicle report for a fleet of vehicles. The table includes, among
other things, the date, current mileage and gallons of fuels purchased. I
would like to set up a query with a calculated field for miles per gal. This
would entail having the query include the previous record's current mileage,
subtracted from the current record's current mileage to get the mileage
between fueling.

SELECT T.Vehicle,
T.Miles,
T.Gallons,
T.FillDate
(SELECT TOP 1 X.Miles
FROM table As X
WHERE X.Vehicle = T.Vehicle
And X.FillDate < T.FillDate
ORDER BY X.FillDate Desc
) As PrevMiles,
(T.Miles - PrevMiles) / T.Miles As MPG
FROM table As T

 

[SusanP via AccessMonster.com]

Worked like a charm... here's what it looks like using my field names:

SELECT T.Vehicle, T.Date, T.CM, T.Gal, (SELECT TOP 1 X.CM From VehicleReport
As X WHERE X.Vehicle = T.Vehicle and X.Date <T.Date ORDER BY X.Date Desc) AS
PrevMiles, (T.CM-PrevMiles)/T.Gal AS MPG

FROM VehicleReport AS T

ORDER BY T.Date;

Thanks for all your help!!!

 

[Marshall Barton]

Worked like a charm... here's what it looks like using my field names:

SELECT T.Vehicle, T.Date, T.CM, T.Gal, (SELECT TOP 1 X.CM From VehicleReport
As X WHERE X.Vehicle = T.Vehicle and X.Date <T.Date ORDER BY X.Date Desc) AS
PrevMiles, (T.CM-PrevMiles)/T.Gal AS MPG

FROM VehicleReport AS T

ORDER BY T.Date;

Thanks for all your help!!!

You're welcome.

A caution is in order here though. Your use of the word
Date is an accident waiting to happen. Better to change it
before you build any more of your application that will have
to be changed. Date (and many more ordinary words) are
reserved words in Access and can be confused between
Access's meaning and what you intended. Best practice is to
always use more descriptive names (such as FillDate).

 

[SusanP via AccessMonster.com]

Will do... again, thanks!