MS Access expression for milage calculation
Posted on 2003-03-21
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?