AnsweredAssumed Answered

Calculating mileages from Actual and Estimated readings

Question asked by philmcgeehan on Jan 16, 2014
Latest reply on Jan 17, 2014 by philmodjunk

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

Outcomes