Solved

Working out fuel consumption

Posted on 2004-04-15
5
452 Views
Last Modified: 2010-05-18
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
Comment
Question by:zoellerman
5 Comments
 
LVL 5

Accepted Solution

by:
Krys_Wilson earned 125 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

by:ala_frosty
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

by:sparab
ID: 10839513
Use follwing simple function for your reports

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

Regards
Sparab
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

839 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