Average Miles Per Gallon Query Require assistance!

[greenfalcon]

i have a quick question... I am having a slight problem with a query that i am using in my database... i am trying to calculate miles per gallon. The query i have set up looks at the previous records odometer value and subtracts the current odometer value to get the miles driven since the previous fill... Here is my sql code.

SELECT fuel_use_tbl.unit_ID, fuel_use_tbl.fuel_use_ID, fuel_use_tbl.fuel_date, fuel_use_tbl.gallons, fuel_use_tbl.odometer, (SELECT TOP 1 Dupe.odometer FROM fuel_use_tbl AS Dupe
WHERE Dupe.unit_ID = fuel_use_tbl.unit_ID
AND Dupe.fuel_date < fuel_use_tbl.fuel_date
ORDER BY Dupe.fuel_date DESC, Dupe.fuel_use_ID) AS PriorValue, [odometer]-[PriorValue] AS [Miles Driven], [Miles Driven]/[gallons] AS Expr1
FROM fuel_use_tbl;

The problem with this code is this... lets say i drive a brand new vehicle (its first record and it has no prior odometer value... the PriorValue reading shows up as nothing (NULL) when i then try to take [odometer]-[PriorValue] it shows up as nothing (NULL) while it should just treat it as [odometer]-0... i need help so that it shows an average mpg for every fill... Thanks for your help.

For a better example this is exactly what it looks like

unit_ID	fuel_use_ID	fuel_date	gallons	odometer	PriorValue	Miles Driven	MPG
1	132	8/11/2007	0	0.00			
1	126	9/11/2007	26	400.00	0	400	15.3846153846154
1	127	9/17/2007	22	700.00	400	300	13.6363636363636
1	131	10/16/2007	23	1,131.00	700	431	18.7391304347826
10	128	9/11/2007	22	0.00			
10	129	9/17/2007	21	500.00	0	500	23.8095238095238
10	130	9/18/2007	28	800.00	500	300	10.7142857142857

Fuel Use ID is my primary Key and it is autoindexed... ive just been messing around with fake data and have changed the dates... thats why it looks a little strange

Greeny

 

[boblarson]

Look into using the Nz function to handle nulls so that the calculations will work. As you can see, if you have nulls it doesn't work. But, the NZ function will handle that.

 

[greenfalcon]

thanks i will check that out.

 

[greenfalcon]

thanks boblarson,

That did the trick, i do not know how i overlooked that function. Thanks again,

Jason Foster

 

[irslan rafique]

Hi, Can you send or write that complete code using NZ that solved you problem please?
I will be very thankful to you.

Thanks

 

[gemma-the-husky]

I am sure you have considered this, but the mpg calc needs full tanks to work properly.

clearly the longer the period considered, the smaller the effect of any full-tank effect.

 

[irslan rafique]

Please please be more specific and help out me by providing complete code. Thanks in advance.