MPG calculation for fleet (1 Viewer)

[PvL]

I seem to break my head over the following problem and can't get it sorted so am turning to this awesome community to see if someone knows the answer

I have a fleet database where I need to calculate MPG. I have looked on google but haven't found the answer (or not easily understandable for me) yet.
To make things worse, half the vehicles in the fleet are reading kilometers and the other half are reading miles. I can convert the kilometers to miles by a query but that is pulling the fleet apart into 2 tables/queries before any mpg calculation is done. And then of course the mpg itself. I understand that it needs to calculate the difference between current and last mileage, but have no idea how, and of course we are not talking 1 vehicle in a table but multiple.
Example table (where odo reading is either miles or km depending on which vehicle)

[tbl_fuelreceipts]
vehicle date odoreading liters price
1 02/02 32000 83 94
4 04/02 12480 112 113
2 04/02 25644 142 153
1 05/02 32845 92 103
4 06/02 13110 97 108
2 05/02 26122 102 113

Data of course is just a made up sample but gives an idea of the structure.
Any help in getting the calculation done (query built) is appreciated

Thanks!

 

[boerbende]

You first have to start to unify your data to either mile or km
Do this with for example a table
vehicle factor
1 0.621371
2 1
etc...

Now you can generate a query which shows liters and km
In fact these are two queries. The first one is getting you the grouped details, the second one is using the details to calculate that you want.

Be aware that the FIRST refill is NOT in the SUM of the total nr of liters used. In fact this is the zero for the km and the refill was to get only a full tank at the "zero" of km
I had also a datefrom (in this case Jan 1st. You can select whatever you want here, it is based on the period you want to look back

SELECT [SUB0].vehicle, ([odomileMax]-[odomilemin])/([SumOfliters] - [firstofliters]) AS MPL
FROM
(
SELECT
Max([odoreading]*[factor]) AS odoMileMax,
Min([odoreading]*[factor]) AS odoMileMin,
Sum(tbl_fuelreceipts.liters) AS SumOfliters,
First(tbl_fuelreceipts.liters) AS FirstOfliters,
tbl_factor.vehicle
FROM tbl_factor INNER JOIN tbl_fuelreceipts ON tbl_factor.vehicle = tbl_fuelreceipts.vehicle
WHERE (((tbl_fuelreceipts.datereceipt)>#1/1/2016#))
GROUP BY tbl_factor.vehicle

) AS [SUB0];

 

[PvL]

Thank you ever so much Ben for your reply!
But stupid enough it is the first bit I struggle with, which is to unify the vehicles as being mileage in one table.
How do I make the table that unifies the vehicles to say "just " mileage.

(For the vehicles I got a seperate table with a field "km_ml" with either a K or M value. So I can run an append query with a "IF../THEN parameter" from both the Fuelreceipts-table and the vehicles table into a new table, similar to the fuelreceipts table but then all vehicles being mileage, but I think I am making it myself way to difficult)

Would you care to tell me HOW you would come to a unified table?
(No need to start even with the second part before part 1 is sorted)

Many thanks again!
PvL

 

[boerbende]

You have already a separate table with a field "km_ml". You are so near a solution yourself

1) Use this table and add a column "factor" (or whatever you want, but I used this name in my query so otherwise you also have to adapt the query)
2) Make this column number, type single
3) Save and open the table.
4) I guess you want to use mile. So for all km vehicles you use as factor 0.621371192 (1 km = 0.621371192 mile). For all the mile vehicles use 1

Now replace in my query the name tbl_factor to your table name.
I assume with that that you table also has the column vehicle because I JOIN the two tables on this column

Success

Ben

 

[boerbende]

Sorry, had used correction factor from mile2km. Corrected this to km2mile.

 

[PvL]

Again, I really appreciate your help, but am still stuck on this (although I am close)
I took my own field out of the vehicle table and added the factor field.
Built the query and ran it (which works!!).
BUT, I have 2 issues:
1) You stated "Make this column number, type single", but the result still gives me a lot of decimals in the result"
I probably can find this out myself after I solve the next issue:
I ran the query as a "make table" query, and can alter the value of the decimals in the newly created table. But every time I run this , I will have to do this, and this should not be necessary. I think it should be possible to have a query that just updates the table with new records when new fuel is purchased.

My current structure of the relevant fields in the 2 tables:

table_vehicle
[registration]
[factor]

table_fuelreceipts
[ID] which is autonumber
[registration]
[driver]
[date]
[odometer]
[price]
[liters]

So what I want is a new table containing all the fields from the table [fuelreceipts] ADDED with the mileage field (which is the calculation with the factor field)
And of course when people add new data into [fuelreceipts] the newly created table should be updated automatically.
My biggest question now, how do I get there (what kind of query or code do I need to use to establish this)

I have a feeling you have a great working fleet database. Hats off for that is an accomplishment. I studied Access just over 10 years ago, and cant remember hardly any of the more interesting modules

 

[boerbende]

Now you took away the km_mile indication. For the future I think it is more clear to have this column as well, but you're the boss.

A lot of decimals is only in the table. In forms or queries you can limit this with the properties by setting this to for example "fixed" with 1 decimal. I never care about my tables, it is all about the views later. You can also do the calculation in your query with the round function:
ROUND([odomileMax]-[odomilemin])/([SumOfliters] - [firstofliters],1)

For the second part: my router died this night during lightning, so I am a little limited here. I will come back to that

Ben

P.S. My "fleet" is my Car and my bicycle

 

[boerbende]

Reading this I think you can solve it even more simple than before
First of all I suggest not to create a whole new table, but just 1 column extra in the table tbl_fuelreadings, namely MPL (or MPG).
Secondly: calculate the MPG directly in the form where you enter the new receipt. You can do this with the afterupdate events of the litersfield, vehicle and odoreading fields (I assume you are a little used to programming here)

Assume your form field for odoreading entry is called frm_odoreading
Assume your form field for vehicle nr is called frm_registration
Assume your form field for liters is called frm_liters
Assume your form field for MPL / MPG is called frm_MP

Put the function CalculateAVG in the afterupdate of the three fields.
Function CalculateAVG stops when one of them is empty, But now the order of entering the data is not important

Private sub frm_vehicle_Afterupdate()
CalculateAVG
End Sub
Private sub frm_liters_Afterupdate()
CalculateAVG
End Sub
Private sub frm_odoreading_Afterupdate()
CalculateAVG
End Sub


Private function CalculateAVG()

Dim PreviousOdo as single
Dim rs as dao.recordset
Dim SQLstr as string
Dim Factor as single


On error goto ErrorHandling

If isnull(frm_odoreading) then exit function ‘ Stop the function when no reading is available
If isnull(frm_registration) then exit function ‘ Stop the function when no vehicle is available
If isnull(frm_liters) then exit function ‘ Stop the function when no liters are available

' Get the factor for this vehicle
Factor = DLookup("Factor", "Table_vehicle", "[registration]= “ & me.frm_registration)

' Build the query to find the previous odoreading for this vehicle
SQLstr = “ SELECT Max([odoreading] ) as MaxOdo “ & _
“ FROM Table_fuelreadings “ & _
“ Where [odoreading] < “ & me.frm_odoreading & _
“ AND [registration] = “ & me.frm_registration

‘ get now the previous mileage for this vehicle and calculate the average
Set rs = CurrentDb.OpenRecordset(SQLstr)
If rs.recordcount = 0 then
Previousodo = 0 ‘ first time usage vehicle
else
Previousodo = rs.Fields("MaxOdo")
endif
rs.Close

‘Calculate now the average
Me.Frm_MP.value = factor * (me.frm_odoreading - previousOdo) / me.frm_Liters

‘ Stop the function normally
exit function

errorhandling:
Msgbox “ Your Error here”
End function