Solved

Working out fuel consumption

Posted on 2004-04-15
5
448 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 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