calculate milage between fueling

[Barb]

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

 

[Marshall Barton]

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

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

 

[Ken Snell [MVP]]

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>

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

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

 

[Ken Snell [MVP]]

My error.... I read your SQL statement (even after copying and pasting it)
incorrectly....

< going away now >

--

Ken Snell
<MS ACCESS MVP>

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

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.

 

[Barb]

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

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

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.

 

[Marshall Barton]

My error.... I read your SQL statement (even after copying and pasting it)
incorrectly....

< going away now >

Aw shucks, I thought I was going to learn something

 

[Ken Snell [MVP]]

< g > it's the other way round.... I learn from you!