MS Access expression for milage calculation

I am making a simple data base for tracking fuel usage for a trucking fleet.  

I have a simple form where a person can take the fuel ticket and record:  the date, the truck number, the mileage, and the number of gallons.  I want to create a report that for entered dates will find the beginning and ending mileage for the time period entered by the users.  This in turn will be used to find the total mileage, total fuel, and average miles per gallon.  

What is confusing me is how to record the value of the mileage for the entered dates.  I'm not sure how to create a table that shows each truck with beginning and ending mileage.  Calculating the total mileage should be straight forward.  Also, how do I sum up all of the fuel used over the time periods?  

I have tried using Between....And for the dates, but I don't understand how to capture the mileage figure which is in the mileage column for the start date and ending date under consideration.

How do I use dates to capture a mileage value (beginning or ending date)?  How do I use dates to sum up the total gallons consumed?

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

cosmos75Connect With a Mentor Commented:
You could create two queries (qryMinMileage & qryMaxMileage).  Create a paramterized query with the tw paramters StartDate and EndDate either in the query or have it pull from textboxes in a form.

Then have the query pull the data (Driver, Date, Mileage)for each driver the and show the MAX and MIN for the milages.

Use another query to find the difference between two

Create a query to calculate the sum of gallons of used between those dates.

FInally, create a qeury to join all that date back again and you should have : Driver, Difference of Milage and Sum of Gallons.  Use that for your report.

Unless each truck is filled up every day you will not get a perfect solution, the system wont know what percentage was used on the days between fill ups.

Saying that though I think that a table with the fields TruckID, RefueldDate, Gallons, Cost and Milometer will give you what you are after.

The query would then be

      ,Sum(Gallons) As TotalGallons
      ,Sum(Cost) As TotalCost
      ,Max(Mileometer) - Min(Mileometer) As Miles
      ,(Max(Mileometer) - MIn(Mileometer)) / Sum(Gallons) as MPG
      ,Sum(Cost) / (Max(Mileometer) - MIn(Mileometer)) As CostPerMile
FROM tblFuel
WHERE RefueledDate Between #01/01/03# And #01/31/03#

Cheers, Andrew
I beleive sorting and grouping feature of the reports will work for you.

1) Create report that lists every start and end mileage pair along with an unbound field that calcs the mileage.

2) Add sorting and grouping levels with total fields to calc the mileage by what ever categories you need (by truck, by user, etc... you can ever add each categories plus and overall)

Then if you don't want to see the individual listing just the calculated totals either shrink the fields down in the details section or set the details section to visible = false .  Your totals will still work fine.

If this isn't what you're after or have more questions just ask.  Hope this has been helpfull
accessaccessAuthor Commented:
I am going to try this approach.  


Let me know if that works or if you need any other help.
All Courses

From novice to tech pro — start learning today.