MS Access expression for milage calculation

Posted on 2003-03-21
Medium Priority
Last Modified: 2008-03-03
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?

Question by:accessaccess
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 28

Expert Comment

ID: 8184400
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

Expert Comment

ID: 8184422
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

Accepted Solution

cosmos75 earned 200 total points
ID: 8188556
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.


Author Comment

ID: 8196329
I am going to try this approach.  


Expert Comment

ID: 8214991

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question