?
Solved

Working out fuel consumption

Posted on 2004-04-15
5
Medium Priority
?
470 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
[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
  • Learn & ask questions
5 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 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