calculate milage between fueling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everyone
I have a table with following fields:
VehicleID,Date,CurrentMilage,Gallons,Cost.
What I'm trying to get is the following: VehicleID, Date,
MilageDrivenSinceLastFueling, Gallons, Cost. Please help

Thanks
Barb
 
Barb said:
I have a table with following fields:
VehicleID,Date,CurrentMilage,Gallons,Cost.
What I'm trying to get is the following: VehicleID, Date,
MilageDrivenSinceLastFueling, Gallons, Cost. Please help
Click to expand...


SELECT T.VehicleID, T.[Date],
T.CurrentMilage - (SELECT Max(X.CurrentMilage)
FROM thetable As X
WHERE X.VehicleID = T.VehicleID
) As MilageSinceLastFueling,
T.Gallons, T.Cost
FROM thetable As T
 
Marsh, I think you also need a criterion related to the "Date" field:

SELECT T.VehicleID, T.[Date],
T.CurrentMilage - (SELECT Max(X.CurrentMilage)
FROM thetable As X
WHERE X.VehicleID = T.VehicleID AND
X.[Date] < T.[Date]
) As MilageSinceLastFueling,
T.Gallons, T.Cost
FROM thetable As T
--

Ken Snell
<MS ACCESS MVP>

Marshall Barton said:
Barb said:
I have a table with following fields:
VehicleID,Date,CurrentMilage,Gallons,Cost.
What I'm trying to get is the following: VehicleID, Date,
MilageDrivenSinceLastFueling, Gallons, Cost. Please help
Click to expand...


SELECT T.VehicleID, T.[Date],
T.CurrentMilage - (SELECT Max(X.CurrentMilage)
FROM thetable As X
WHERE X.VehicleID = T.VehicleID
) As MilageSinceLastFueling,
T.Gallons, T.Cost
FROM thetable As T
Click to expand...
 
Ken said:
Marsh, I think you also need a criterion related to the "Date" field:

SELECT T.VehicleID, T.[Date],
T.CurrentMilage - (SELECT Max(X.CurrentMilage)
FROM thetable As X
WHERE X.VehicleID = T.VehicleID AND
X.[Date] < T.[Date]
) As MilageSinceLastFueling,
T.Gallons, T.Cost
FROM thetable As T
Click to expand...


Gee, Ken, I don't see where the OP metioned anthing bout a
date criteria, or were you referring to possibility that the
odometer might have rolled over?

I might be getting punchy in this heat, but I don't see what
you're suggesting here, Ken.
 
My error.... I read your SQL statement (even after copying and pasting it)
incorrectly....

< going away now >

--

Ken Snell
<MS ACCESS MVP>



Marshall Barton said:
Ken said:
Marsh, I think you also need a criterion related to the "Date" field:

SELECT T.VehicleID, T.[Date],
T.CurrentMilage - (SELECT Max(X.CurrentMilage)
FROM thetable As X
WHERE X.VehicleID = T.VehicleID AND
X.[Date] < T.[Date]
) As MilageSinceLastFueling,
T.Gallons, T.Cost
FROM thetable As T
Click to expand...


Gee, Ken, I don't see where the OP metioned anthing bout a
date criteria, or were you referring to possibility that the
odometer might have rolled over?

I might be getting punchy in this heat, but I don't see what
you're suggesting here, Ken.
Click to expand...
 
Thank you Ken and Marshall. I'll try it out first thing tomorrow, you guys
are so helpfull. I've spent two days trying to figure out how to do it.

Thanks again
Barb

Marshall Barton said:
Ken said:
Marsh, I think you also need a criterion related to the "Date" field:

SELECT T.VehicleID, T.[Date],
T.CurrentMilage - (SELECT Max(X.CurrentMilage)
FROM thetable As X
WHERE X.VehicleID = T.VehicleID AND
X.[Date] < T.[Date]
) As MilageSinceLastFueling,
T.Gallons, T.Cost
FROM thetable As T
Click to expand...


Gee, Ken, I don't see where the OP metioned anthing bout a
date criteria, or were you referring to possibility that the
odometer might have rolled over?

I might be getting punchy in this heat, but I don't see what
you're suggesting here, Ken.
Click to expand...
 
Ken said:
My error.... I read your SQL statement (even after copying and pasting it)
incorrectly....

< going away now >
Click to expand...


Aw shucks, I thought I was going to learn something
 
Back
Top