Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Working out fuel consumption

Posted on 2004-04-15
5
Medium Priority
?
484 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
3 Comments
 
LVL 5

Accepted Solution

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

571 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