Yearly odometer reading- subtract from last years (1 Viewer)

Hello,

I did attempt to search and research this out, but of what I did find that was similar, I did not understand fully to utilize it. I'm still learning.... lots and lots of learning...



I have a database that I modified from MS templates Fleet Management. I am in the process of creating what I need from it. We have a car fleet of leased, not owned, vehicles. So every two/three years, the plates get transferred to a new VIN#. Each year, I am given the odometer reading as of such and such date.

What I created/modified is essentially a table that records the reading, VIN#, license plate#, driver and date recorded among a few other things.



What I am trying to create, and need help with, is in the report (also from the template, just modified) I want to use last years odometer reading and subtract it from current years odometer reading to auto calculate miles driven in that year for that particular VIN# and driver.
So, as an example:


Row 1 in the table:
driver, VIN#, Odometer Reading, Date (last year)

Row 2 in the table:

driver, VIN#, Odometer Reading, Date (this year)


Wan to learn how to subtract Odometer reading (last year) from Odometer reading (this year) = number of miles driven between the two dates.


All of this was a very manual process previously recorded in excel. Each year has it's own workbook. Making it very hard to look at historical information in comparison to current.


Help?

I am learning, I have very little background with Access, just from using it.

Hi beancounter and welcome to AWF.
Your post was moderated. Now approved.
Its not clear from your post what exactly you want help with.
Please clarify.

isladogs said:

Hi beancounter and welcome to AWF.
Your post was moderated. Now approved.
Its not clear from your post what exactly you want help with.
Please clarify.

Click to expand...

I've modified the post. I hope that clears up what I'm asking?

beancounter13 said:

I've modified the post. I hope that clears up what I'm asking?

Click to expand...

Hi. One way to do this is to use DLookup(), but it might run slow.

theDBguy said:

Hi. One way to do this is to use DLookup(), but it might run slow.

Click to expand...

I will research how to use that function. Thanks for pointing me in the right direction!

Thanks for clarifying your first post.
I suggest you use a subquery for this using the same method as in this link from Allen Browne's website http://allenbrowne.com/subquery-01.html#AnotherRecord

Why is the driver involved in this lookup? If you are trying to track the miles on the vehicle, the driver shouldn't be relevant. Also, the vehicle might not have been in service last year so wouldn't you want the mileage for the year to date?

How is your table designed and when do mileage records get recorded? Once per year in January for previous year. Plus once any time during the year when the vehicle comes into service?