miles driven and per gallon formulas

[dkaf]

I have a database where I collect vehicle number, date, number of gallons of fuel put in at fuel pump and odometer reading at time of fueling. I am writing a report and need to formulate miles driven and miles per gallon. Therefore I need 2 formulas - 1 that will look at (per vehicle) the fuel date from the last fuel to get odometer reading, use that odometer to subtract this fueling odometer to get miles driven. Then I need a second formula to take gallons received this fueling and divide it by the miles driven (last formula) to get miles per gallon.

Is this possible? I am a novice and have not had much training in formulas.

 

[dhookom]

Please provide some sample records with the desired calculated values. Make sure you have a couple different vehicles represented.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Hi,

gallons received this fueling and divide it by the miles driven (last formula) to get miles per gallon.

The UNITS, in this case MILES per GALLON or MILES/GALLON, tell you what to do.

From your statement above, you would have GALLONS/MILES DRIVEN, giving your GALLONS per MILE. That is not what you want, is it?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dkaf]

Sample data fron data base.

ID Bus Date Gallons Mileage
1 1 4/7/2009 56 10330
2 2 4/6/2009 50 22675
3 1 4/3/2009 66 10110
4 2 4/3/2009 55 22225
5 1 4/4/2009 45 10220
6 2 4/4/2009 52 22445

I need Bus 1 to look at the fueling from 4/7/09 - mileage 10330 - look at the last time it fueled 4/4/09 10220 - formulate the mileage run (10330 - 10220 = 110 miles). Then take the gallons from fueling of 4/7/09 56 gallons to calculate the miles per gallon - 110 miles divided by 56 = 1.964 mpg.

My report groups by bus and the fueling is in date order and the mi run and mpg is the 2 formulas I need:

Bus 1 4/7/09 56 gal 10330 odometer 110 mi. 1.964mpg
4/4/09 45 10220 110 2.444
4/3/09 66 10110 110 1.666
Bus 2 4/7/09 ...etc

 

[dhookom]

Try SQL of:

SELECT ID, Bus, [Date], Gallons, Mileage, 
Mileage - (SELECT Max(Mileage) FROM table a WHERE a.Bus = Table.bus And A.Mileage < Table.Mileage) as MilesTravel
FROM table;

Then, in your report, you can do the math to calculate the mpg.


Duane
Hook'D on Access
MS Access MVP