SQL Query to Calc Gas Mileage

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.

re: SQL Query to Calc Gas Mileage

james.eaton@gmail.com wrote:[color=blue]
> 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.[/color]

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

re: SQL Query to Calc Gas Mileage

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

re: SQL Query to Calc Gas Mileage

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

re: SQL Query to Calc Gas Mileage

james.eaton@gmail.com wrote:[color=blue]
> 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[/color]

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