Create a calculation based on field in the previous record

  • Thread starter Thread starter SusanP
  • Start date Start date
S

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?
 
SusanP said:
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.
Click to expand...

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
 
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!!!
 
SusanP said:
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!!!
Click to expand...


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).
 
Back
Top