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_purchased / mileage_at_purchase[n] -
mileage_at_purchase[n-1]). I can't figure out I can easilly get
gallons_purchased and mileage_at_purchase into a SQL statement, but how
do i reference the last entry's mileage_at_purchase in my SQL statement
so I can make the calculation?

prikey mileage_at_purchase gallons_purchased 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*********@gmail.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_purchased / mileage_at_purchase[n] -
mileage_at_purchase[n-1]). I can't figure out I can easilly get
gallons_purchased and mileage_at_purchase into a SQL statement, but how
do i reference the last entry's mileage_at_purchase in my SQL statement
so I can make the calculation?

prikey mileage_at_purchase gallons_purchased 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_purchase, gallons_purchased, (Nz(SELECT
A.mileage_at_purchase FROM tblMileage AS A WHERE A.prikey =
tblMileage.prikey - 1)) AS [PrevMileage], (mileage_at_purchase -
Nz([PrevMileage])) / gallons_purchased AS gas_mileage FROM tblMileage;

Result:

prikey mileage_at_purchase gallons_purchased 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("mileage_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_purchase
gallons_purchased 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*********@gmail.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("mileage_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_purchase
gallons_purchased 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.prikey, tblMileage.vehicleID, tblMileage.ReceiptDate,
tblMileage.mileage_at_purchase, tblMileage.gallons_purchased,
(Nz(SELECT A.mileage_at_purchase FROM tblMileage AS A WHERE A.vehicleID
= tblMileage.vehicleID AND A.ReceiptDate IN (SELECT Max(B.ReceiptDate)
FROM tblMileage As B WHERE B.ReceiptDate < tblMileage.ReceiptDate AND
B.vehicleID = tblMileage.vehicleID))) AS [PrevMileage],
(mileage_at_purchase - Nz([PrevMileage])) / gallons_purchased AS
gas_mileage
FROM tblMileage ORDER BY vehicleID, ReceiptDate;

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

James A. Fortune