Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

Working out fuel consumption

Posted on 2004-04-15
Medium Priority
476 Views
I am trying to work out a way of calculating fuel consumption over a given time period.
All fuel issues are entered into a form.
The date,vehicle fleet no,fuel issue amount(litres) & mileage(some vehicles are in miles,some in kilometres)
I was hoping to be able to create a report that would allow any vehicle to be selected, any time range & it would show the the miles per gallon.
I hope somebody can help
Thanks
0
Question by:zoellerman
[X]
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

LVL 5

Accepted Solution

Krys_Wilson earned 500 total points
ID: 10837596
Zoellerman,

To create the calculations you will need Amount of fuel used (Not just issue amount) and mileage used.  This data can be calculated if you have Fuel Issue amount and Fuel Amount(Amt of fuel at end of trip) and also Begining Mileage and Ending Mileage.

If you have that then you can easily create a report.  If not, you may need to add that data to the tables before this can be done.

Chris
0

LVL 7

Expert Comment

ID: 10838727
First off, each Fuel Issue MUST fill the tank. Secondly, you never fill a vehicle twice in the same day. If you do, you need to add a new field to your table that would be a unique identifier, or start recording the time along with the date (preferrable I would say).

Let's say your table is named tblFuelIssue

Query1: [Here's where you pick out the fleet cars and the date of the current fill along with the date of the most recent fill]
SELECT FL.FleetNumber, Max(FL.IssueDate) AS FLissueDate, FH.IssueDate AS FHIssueDate
FROM tblFuel AS FL INNER JOIN tblFuel AS FH ON FL.FleetNumber = FH.FleetNumber
WHERE (((FL.IssueDate)<[FH].[IssueDate]))
GROUP BY FL.FleetNumber, FH.IssueDate;

Query2: [Here's where you pull the issue records based on which ones match up with the ones selected in Query1.
SELECT Query1.FleetNumber, Query1.FHIssueDate AS [Date], [FH].[odometer]-[Fl].[Odometer] AS Miles, FH.fuel AS FuelConsumed, IIf([FuelConsumed]=0,Null,[Miles]/[FuelConsumed]) AS mileage
FROM (Query1 INNER JOIN tblFuel AS FL ON (Query1.FLissueDate = FL.IssueDate) AND (Query1.FleetNumber = FL.FleetNumber)) INNER JOIN tblFuel AS FH ON (Query1.FHIssueDate = FH.IssueDate) AND (Query1.FleetNumber = FH.FleetNumber);

I could show you how to do it in one query, but Access REALLY hates using them, so best just to leave it as two.

Stephen
0

LVL 11

Expert Comment

ID: 10839513
Use follwing simple function for your reports

Elapsed time = Format\$(EndTime - StartTime, "HH:MM:SS")

Regards
Sparab
0

Featured Post

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month8 days, 6 hours left to enroll