mpg calculation

[papavictorlima]

hi all
I am building a database for our fleet of vehicles. One of the reports that I require is mpg calculation.
I have a table "fuelreceipts" which contain
[vehicle]
[date]
[odoreading]
[price]
[liters]
I know mpg is calculated from the difference between odoreadings, but as the table contains several different vehicles, I don't know how to make a record look for the previous odoreading. Well, I could do it in a query PER vehicle, but as I need a monthly report to display all vehicles with their mpg's I am sure there is an easier way
To make things even more challenging, some vehicles have their readings in miles....
Of course I could give more table info, but not sure what is required.



Can someone point me in the direction? ANY is much appreciated. Thanks

[ranman256]

we use 2 fields on the record, ODstart, ODend to do it easily.
But since you do not have this, it would have to be done programmatically thru VB.
I would add an extra field, MPG (kpL?)
build a query to sort the records by Veh, Date
then run this code

Public Sub CalcMPG()
Dim db As Database
Dim rst   'As Recordset
Dim qdf As QueryDef
Dim vCurr, vPrev, vVeh, vDate, vOD, vGal, vMPG, vPrevOD, vPrevDte, vPrevV
Dim sSql As String
' table "fuelreceipts" which contain
'[vehicle]
'[Date]
'[odoreading]
'[price]
'[liters]

DoCmd.SetWarnings False
Set db = CurrentDb
Set qdf = db.QueryDefs("qsFuelEstSort")  'sort by vehicle,date
Set rst = qdf.OpenRecordset(dbOpenDynaset)
vPrevDup = "*&%"

With rst
    While Not .EOF
        vVeh = .Fields("vehicle")
        vDate = .Fields("date")
        vOD = .Fields("odoreading")
        vGal = .Fields("liters")
        
        If vVeh <> "" Then
            If vPrevV = vVeh Then
                vMPG = (vOD - vPrevOD) / vGal
                       'update the previous record
                 sSql = "update FuelReceipts set [MPG]=" & vMPG & " where [vehicle]='" & vVeh & "' and [date]=#" & vPrevDte & "#"
                 DoCmd.RunSQL sSql
            End If
        End If
        vPrevV = vVeh
        vPrevOD = vOD
        
       .MoveNext
    Wend
End With
DoCmd.SetWarnings True

Set qdf = Nothing
Set rst = Nothing
Set db = Nothing
End Sub

[orange]

pvl,

I think you would benefit from working through this free tutorial from RogersAccessLibrary.
It will show you how to describe your "problem/opportunity" in business terms. And lead you through a consistent process to identify the things of interest, their attributes and the relationships between them based on your business. The things of interest become your tables and the attributes become fields. This approach will lead you to a blueprint of the database required to support your business.
The tutorial will take about 30-45 minutes to work through, and you must do the work to get the benefit. But you will learn concepts that you can use with any database.
The tutorial comes with supportive documentation and a solution.
Good luck

[ssanfu]

The story goes "Once upon a time, long, long ago........"
I created a MPG calculator (one of my first dBs) in an effort to help know when to do maintenance (and to learn Access). If MPG dropped... time for tune up.

Eventually I went back to a log book I keep in the truck. But it was a good learning experience.
Maybe it will help you. I think only the first truck (1987) has test data.