Associating a calculated value with another rowID

[ledgerr.rob]

Forum,

Access 2013
windows 7

I am working with fueling up cars. I have a table tblFuelEntry in which data associated with fueling up a car is entered. Information such as Fuel date/time, tankMiles, Gallons, CarID, GasStationID are all entered. In my qryMPG I calculate the MPG for each fill up. Here in lies my problem. The MPG that is calculated is actually indicative of the previous fill-up's gas station for that car.

How can I tell the computer to calculate the MPG and then associate that value with the previous gas station used to fill-up that car?

I've been playing around with the Date/Time serial values as I am guessing I could use those values to select on as the fill-ups aren't always entered in ascending date order...

I would appreciate any direction that may be offered in this endeavor.
thanks,
rob

 

[plog]

First this is made a lot easier if tblFuelEntry has an autonumber ID field for each record. Let's call that field EntryID. So then in your query you identify the previous EntryID for that car.

My recommendation for this is to create a function in a module that you pass [CarID] and [Fuel date/time] to. The function would use those two pieces of data and return an EntryID to identify the previous record for that car. Here's some psuedo code for that function:

Function get_PriorFuelEntry(in_Car, in_Date)

set PriorTime to DMax that finds [Fuel Date/Time] for in_Car, occuring before in_Date
set ReturnValue to Dlookup that finds EntryID of record with PriorTime and in_Car
Return ReturnValue

End Function

 

[CJ_London]

I think you are looking at the wrong way round - I suggest you want the previous fillup to find the next mileage.

You are right about using the date/time field although you could use mileage instead which is easier if you use a dmin function

 select *, dmin("mileage","mytable","mileage>" & mileage) as endmileage
 from mytable

alternatively you could use a subquery whuch will be faster, particularly if there are a large number of records

 select *,(select first(mileage) from myTable as T WHERE filldate>myTable.filldate order by filldate desc) as endmileage
 from myTable

 

[ledgerr.rob]

Thanks for both of your responses. I agree with CJ_London that utilizing mileage would be an ideal way. Unfortunately, my wife sometimes 'forgets' to keep her receipts/keep the record for us to enter into the DB. As such, her entries are spotty for her car. The intent behind this project is to monitor the MPG for our vehicles and see if any changes in MPG that might be related to a needed repair or tire inflation etc... The fuel station thing was kind of a secondary thing that popped in afterwards. This has just fueled (no pun intended) my interest to see if our cars get different or varying MPG for each station. I added a drivetype (city or highway) to my tblFuelEntry table to separate.

Because of the inconsistant nature of the data entry in my situation, I think I will first attempt user plog's suggestion first. But for testing and learning i'll try user CJ-London's suggestion as well.

For my first attempt using plog's suggestion I'm sure I'll have some questions as I try to work through this solution today.

The help and direction by both of you have been great and much appreciated.
rob