SQL Query to Calc Gas Mileage

[james.eaton]

Hi All,

I have some experience with SQL, but this one stumps me. I have
created a table to track my gas purchases (see below). But, each time
I enter a new purchase, I'm manually calculating gas mileage as
(gallons_purcha sed / mileage_at_purc hase[n] -
mileage_at_purc hase[n-1]). I can't figure out I can easilly get
gallons_purchas ed and mileage_at_purc hase into a SQL statement, but how
do i reference the last entry's mileage_at_purc hase in my SQL statement
so I can make the calculation?

prikey mileage_at_purc hase gallons_purchas ed gas_mileage*
---------------------------------------------------------------
1 176 3.447 51.06
2 329 3.167 48.31
3 506 3.027 58.47
4 650 3.126 46.07

*I want to calculate this column.

[jimfortune]

ja*********@gma il.com wrote:

Hi All,

I have some experience with SQL, but this one stumps me. I have
created a table to track my gas purchases (see below). But, each time
I enter a new purchase, I'm manually calculating gas mileage as
(gallons_purcha sed / mileage_at_purc hase[n] -
mileage_at_purc hase[n-1]). I can't figure out I can easilly get
gallons_purchas ed and mileage_at_purc hase into a SQL statement, but how
do i reference the last entry's mileage_at_purc hase in my SQL statement
so I can make the calculation?

prikey mileage_at_purc hase gallons_purchas ed gas_mileage*
---------------------------------------------------------------
1 176 3.447 51.06
2 329 3.167 48.31
3 506 3.027 58.47
4 650 3.126 46.07

*I want to calculate this column.

SELECT prikey, mileage_at_purc hase, gallons_purchas ed, (Nz(SELECT
A.mileage_at_pu rchase FROM tblMileage AS A WHERE A.prikey =
tblMileage.prik ey - 1)) AS [PrevMileage], (mileage_at_pur chase -
Nz([PrevMileage])) / gallons_purchas ed AS gas_mileage FROM tblMileage;

Result:

prikey mileage_at_purc hase gallons_purchas ed PrevMileage gas_mileage
1 176 3.447 51.05889179
2 329 3.167 176 48.3107041364
3 506 3.027 329 58.4737363726
4 650 3.126 506 46.0652591171

Apply your favorate rounding method.

James A. Fortune

[james.eaton]

Ok, I've answered the first half of my own question. From a MSDN
article, I can now use the following to reference the last primary
key's values:

DLookUp("mileag e_at_purchase", "tblGas", "[prikey]=" & [prikey]-1)

I now have a delima. What if my primary keys aren't sequential.
Specifically, what if I am tracking two vehicles by adding a 'vehicle'
field? Or what if I enter reciepts out of date order? I want to look
back to the last date I filled up on for the specific vehicle. The
table now looks like this:

prikey vehicle date mileage_at_purc hase
gallons_purchas ed gas_mileage*
-----------------------------------------------------------------------------------------------------------------------
1 1 01-Jan-05 176
3.447 51.06
2 1 15-Jan-05 329
3.167 48.31
3 1 31-Jan-05 506
3.027 58.47
4 1 07-Feb-05 650
3.126 46.07
5 2 08-Feb-05 250
14.221 24.02
6 1 23-Feb-05 811
3.609 44.61

[james.eaton]

Now that's a neat trick. That's aliasing, right? What does the Nz
stand for?

[jimfortune]

ja*********@gma il.com wrote:

Ok, I've answered the first half of my own question. From a MSDN
article, I can now use the following to reference the last primary
key's values:

DLookUp("mileag e_at_purchase", "tblGas", "[prikey]=" & [prikey]-1)

I now have a delima. What if my primary keys aren't sequential.
Specifically, what if I am tracking two vehicles by adding a 'vehicle'
field? Or what if I enter reciepts out of date order? I want to look
back to the last date I filled up on for the specific vehicle. The
table now looks like this:

prikey vehicle date mileage_at_purc hase
gallons_purchas ed gas_mileage*
-----------------------------------------------------------------------------------------------------------------------
1 1 01-Jan-05 176
3.447 51.06
2 1 15-Jan-05 329
3.167 48.31
3 1 31-Jan-05 506
3.027 58.47
4 1 07-Feb-05 650
3.126 46.07
5 2 08-Feb-05 250
14.221 24.02
6 1 23-Feb-05 811
3.609 44.61

SELECT tblMileage.prik ey, tblMileage.vehi cleID, tblMileage.Rece iptDate,
tblMileage.mile age_at_purchase , tblMileage.gall ons_purchased,
(Nz(SELECT A.mileage_at_pu rchase FROM tblMileage AS A WHERE A.vehicleID
= tblMileage.vehi cleID AND A.ReceiptDate IN (SELECT Max(B.ReceiptDa te)
FROM tblMileage As B WHERE B.ReceiptDate < tblMileage.Rece iptDate AND
B.vehicleID = tblMileage.vehi cleID))) AS [PrevMileage],
(mileage_at_pur chase - Nz([PrevMileage])) / gallons_purchas ed AS
gas_mileage
FROM tblMileage ORDER BY vehicleID, ReceiptDate;

Note: I used ReceiptDate instead of Date and vehicleID instead of
vehicle.

James A. Fortune