5 Replies Latest reply on Jan 17, 2014 6:27 AM by philmodjunk

    Calculating mileages from Actual and Estimated readings

    philmcgeehan

      Title

      Calculating mileages from Actual and Estimated readings

      Post

           I would like to be able to record a vehicles mileage in our database and have the latest reading display.
           So far, so good.
           I have a Vehicles table and a VehicleMileage related table; at the moment I use a Summary field with the Max of the ActualMileage to I get the latest reading.

           However, to complicate things; we receive monthly approximate miles travelled for each vehicle, and I would like to create an Calculated Mileage that is based on the last actual mileage.
           I've put the monthly mileages in a separate field in the VehicMileage table, each in it's own record.

           I think I need something like this:

           CalculatedMilage = s_MaxActualMileage + Sum ( MonthlyEstimated (if after date of last mileage) )

           i.e.

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                          

      Actual Mileage

                     
                          

      Monthly Estimated

                     
                          

      Calculated

                     
                          info
                          

                               20,004

                     
                          

                                

                     
                          

                               20,004

                     
                          20004
                          

                                

                     
                          

                               2,462

                     
                          

                               22,466

                     
                          20004+(2462)
                          

                                

                     
                          

                               1,912

                     
                          

                               24,378

                     
                          20004+(2462+1912)
                          

                               24,404

                     
                          

                                

                     
                          

                               24,404

                     
                          24404
                          

                                

                     
                          

                               2,415

                     
                          

                               26,819

                     
                          24404+(2415)
                          

                                

                     
                          

                               2,014

                     
                          

                               28,833

                     
                          24404+(2415+2014)
                          

                               28,756

                     
                          

                                

                     
                          

                               28,756

                     
                          28756

           As you can see sometimes we may have over estimated and sometimes underestimated. But I want to find the last Actual Mileage + the sum of the Monthly miles if they're after the previous Actual.

           Hope I haven't confused things too much!
           Thanks in advance,
           Phil