logic help needed - MPG calculation

[SuzyQ]

I need some help on how to go about creating a report of miles per gallon for
each vehicle the county owns.

We have 4 road districts, so the report will be gouped by district. Each
vehicle number (EquipmentCode) is tied to a district so I can look at the
Vehicle inventory table to determine which district the vehicle belongs to.
My fuel/mileage table looks like this

EquipmentCode - text (5) - foreign key from

FillDate - Date

InventoryID - long (foreign key - to determine which tank the fuel was taken
from for inventory purposes)

Fuel- Text (will contain either "Unleaded" or "Diesel" will be entered
automatically if taken from inventory (inventoryID combo box column) or will
be selected by the user from the value list if purchased)

FuelGallons - double

EquipmentMileage - double

CostPerGal - double (will be entered automatically if taken from inventory
(inventoryID combo box column - avg of inventory cost) or will be entered by
user acutal purchase price if purchased)

InventoryDeducted - boolean

EquipmentFuelID - autonumber - primary key


What I want to do is for a specified date range I want to calculate miles
per gallon, but my dilema is that for the first record in the range, I need
to ignore the number of gallons to accurately determine MPG. Is there an
easy way to do this?

 

[Ken Snell [MVP]]

First, I suggest that you use Currency datatype for FuelGallons,
EquipmentMileage, and CostPerGal. Even though not all of these fields are
"money", Currency avoids the floating / imprecise nature that you get with
Double data type, and avoids weird rounding or other imprecise errors from
ACCESS when doing calculations.

Second, how do you intend to calculate a MPG value without number of gallons
for that first record? Do you plan to assign an arbitrary value to MPG for
that record? Or do you have some other thought in mind?

 

[KARL DEWEY]

Try these two queries --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage, Min(FillDate)
AS StartDate
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(FuelGallons) – IIF(FillDate = StartDate,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) – IIF(FillDate =
StartDate, FuelGallons, 0) AS MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

 

[SuzyQ]

Thanks for the suggestion on using the currency data type. I should have for
the costPerGal field anyway and I set the others to specific decimals, but I
see your point.

Second the number of gallons on a record is the number of gallons for the
fill up on that day. For MPG calculation that number needs to be ignored (or
0) but has to be entered correctly in the data for other date ranges. For
the calculation I planned to use the max value of (mileage - the min value of
mileage) \ sum of gallons however for the starting point (min value of
mileage) the value of gallons cannot be included in the sum of gallons
becuase number of gallons entered is fuel that was used (miles that occurred)
prior to fill up. I.E. See sample data below set up for a result of 20 MPG

Vehicle FillDate Gallons Mileage
0100 03/24/2009 30 15000
0100 04/01/2009 30 15600
0100 04/07/2009 30 16200
0100 04/14/2009 30 16800

date range 03/24/2009 - 04/07/2009
16200-15000 = 1200 miles
sum of 03/24/2009 - 04/07/2009 gallons = 90
1200/90 = 13.3 MPG

Calculation should be 1200/60 = 20 MPG because the first 30 is gas for miles
driven prior to fill up on 03/24/2009

Does that make more sense?

 

[SuzyQ]

This looks like it might work. However has the unlikely but possible problem
of what if they fill up twice in one day and that happens to be the StartDate?

Try these two queries --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage, Min(FillDate)
AS StartDate
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(FuelGallons) – IIF(FillDate = StartDate,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) – IIF(FillDate =
StartDate, FuelGallons, 0) AS MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

I need some help on how to go about creating a report of miles per gallon for
each vehicle the county owns.

We have 4 road districts, so the report will be gouped by district. Each
vehicle number (EquipmentCode) is tied to a district so I can look at the
Vehicle inventory table to determine which district the vehicle belongs to.
My fuel/mileage table looks like this

EquipmentCode - text (5) - foreign key from

FillDate - Date

InventoryID - long (foreign key - to determine which tank the fuel was taken
from for inventory purposes)

Fuel- Text (will contain either "Unleaded" or "Diesel" will be entered
automatically if taken from inventory (inventoryID combo box column) or will
be selected by the user from the value list if purchased)

FuelGallons - double

EquipmentMileage - double

CostPerGal - double (will be entered automatically if taken from inventory
(inventoryID combo box column - avg of inventory cost) or will be entered by
user acutal purchase price if purchased)

InventoryDeducted - boolean

EquipmentFuelID - autonumber - primary key


What I want to do is for a specified date range I want to calculate miles
per gallon, but my dilema is that for the first record in the range, I need
to ignore the number of gallons to accurately determine MPG. Is there an
easy way to do this?

 

[Ken Snell [MVP]]

I understand your setup and what you want to calculate. Before I suggest a
query, some additional questions:

1) Will the MPG always be calculated based on all records in the table? Or
will you be entering/using a specific date range to calculate the MPG?

2) In your reply to Karl, you indicated that it's possible for more than one
fillup on one day. In that case, are you also storing the time with the
date?

3) What is the primary key field(s) for your table?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[SuzyQ]

1. it will always use a date range
2. No - if I sort by mileage, the minimum mileage is the first record
3. EquipmentFuelID - autonumber - primary key

 

[KARL DEWEY]

If the time is included it will work fine.

But remember that the End_Date needs to encompass the full days time also.

So entering 5/27/09 as End_Date will only include yesterdays fill-ups and
not today's as it does not have time included.

This looks like it might work. However has the unlikely but possible problem
of what if they fill up twice in one day and that happens to be the StartDate?

Try these two queries --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage, Min(FillDate)
AS StartDate
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(FuelGallons) – IIF(FillDate = StartDate,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) – IIF(FillDate =
StartDate, FuelGallons, 0) AS MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

I need some help on how to go about creating a report of miles per gallon for
each vehicle the county owns.

We have 4 road districts, so the report will be gouped by district. Each
vehicle number (EquipmentCode) is tied to a district so I can look at the
Vehicle inventory table to determine which district the vehicle belongs to.
My fuel/mileage table looks like this

EquipmentCode - text (5) - foreign key from

FillDate - Date

InventoryID - long (foreign key - to determine which tank the fuel was taken
from for inventory purposes)

Fuel- Text (will contain either "Unleaded" or "Diesel" will be entered
automatically if taken from inventory (inventoryID combo box column) or will
be selected by the user from the value list if purchased)

FuelGallons - double

EquipmentMileage - double

CostPerGal - double (will be entered automatically if taken from inventory
(inventoryID combo box column - avg of inventory cost) or will be entered by
user acutal purchase price if purchased)

InventoryDeducted - boolean

EquipmentFuelID - autonumber - primary key


What I want to do is for a specified date range I want to calculate miles
per gallon, but my dilema is that for the first record in the range, I need
to ignore the number of gallons to accurately determine MPG. Is there an
easy way to do this?

 

[Ken Snell [MVP]]

I'll wait until you've tested the suggested query from Karl. If that doesn't
work for you, post back in this thread and I'll put together a suggested
query for you.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[SuzyQ]

My users will not want to be responsible for entering times when entering
date ranges for the query, especially for that rare occasion when there are
multiple fill ups. How do I, for the report request form, automaticallay
tack on the time, beginning of the day for start date and end of day for end
date so that no records are missed?

If the time is included it will work fine.

But remember that the End_Date needs to encompass the full days time also.

So entering 5/27/09 as End_Date will only include yesterdays fill-ups and
not today's as it does not have time included.

This looks like it might work. However has the unlikely but possible problem
of what if they fill up twice in one day and that happens to be the StartDate?

Try these two queries --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage, Min(FillDate)
AS StartDate
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(FuelGallons) – IIF(FillDate = StartDate,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) – IIF(FillDate =
StartDate, FuelGallons, 0) AS MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];


:

I need some help on how to go about creating a report of miles per gallon for
each vehicle the county owns.

We have 4 road districts, so the report will be gouped by district. Each
vehicle number (EquipmentCode) is tied to a district so I can look at the
Vehicle inventory table to determine which district the vehicle belongs to.
My fuel/mileage table looks like this

EquipmentCode - text (5) - foreign key from

FillDate - Date

InventoryID - long (foreign key - to determine which tank the fuel was taken
from for inventory purposes)

Fuel- Text (will contain either "Unleaded" or "Diesel" will be entered
automatically if taken from inventory (inventoryID combo box column) or will
be selected by the user from the value list if purchased)

FuelGallons - double

EquipmentMileage - double

CostPerGal - double (will be entered automatically if taken from inventory
(inventoryID combo box column - avg of inventory cost) or will be entered by
user acutal purchase price if purchased)

InventoryDeducted - boolean

EquipmentFuelID - autonumber - primary key


What I want to do is for a specified date range I want to calculate miles
per gallon, but my dilema is that for the first record in the range, I need
to ignore the number of gallons to accurately determine MPG. Is there an
easy way to do this?

 

[KARL DEWEY]

I was talking about the FillDate to include time.

Below will include all of the end date.
WHERE FillDate Between [Enter Start] And [Enter End]+0.9999;

My users will not want to be responsible for entering times when entering
date ranges for the query, especially for that rare occasion when there are
multiple fill ups. How do I, for the report request form, automaticallay
tack on the time, beginning of the day for start date and end of day for end
date so that no records are missed?

If the time is included it will work fine.

But remember that the End_Date needs to encompass the full days time also.

So entering 5/27/09 as End_Date will only include yesterdays fill-ups and
not today's as it does not have time included.

This looks like it might work. However has the unlikely but possible problem
of what if they fill up twice in one day and that happens to be the StartDate?

:

Try these two queries --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage, Min(FillDate)
AS StartDate
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(FuelGallons) – IIF(FillDate = StartDate,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) – IIF(FillDate =
StartDate, FuelGallons, 0) AS MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];


:

I need some help on how to go about creating a report of miles per gallon for
each vehicle the county owns.

We have 4 road districts, so the report will be gouped by district. Each
vehicle number (EquipmentCode) is tied to a district so I can look at the
Vehicle inventory table to determine which district the vehicle belongs to.
My fuel/mileage table looks like this

EquipmentCode - text (5) - foreign key from

FillDate - Date

InventoryID - long (foreign key - to determine which tank the fuel was taken
from for inventory purposes)

Fuel- Text (will contain either "Unleaded" or "Diesel" will be entered
automatically if taken from inventory (inventoryID combo box column) or will
be selected by the user from the value list if purchased)

FuelGallons - double

EquipmentMileage - double

CostPerGal - double (will be entered automatically if taken from inventory
(inventoryID combo box column - avg of inventory cost) or will be entered by
user acutal purchase price if purchased)

InventoryDeducted - boolean

EquipmentFuelID - autonumber - primary key


What I want to do is for a specified date range I want to calculate miles
per gallon, but my dilema is that for the first record in the range, I need
to ignore the number of gallons to accurately determine MPG. Is there an
easy way to do this?

 

[KARL DEWEY]

DID NOT TEST BEFORE POSTING --

Below will include all of the end date.
WHERE FillDate Between CVDate([Enter Start]) And CVDate([Enter End])+0.9999;

I was talking about the FillDate to include time.

Below will include all of the end date.
WHERE FillDate Between [Enter Start] And [Enter End]+0.9999;

My users will not want to be responsible for entering times when entering
date ranges for the query, especially for that rare occasion when there are
multiple fill ups. How do I, for the report request form, automaticallay
tack on the time, beginning of the day for start date and end of day for end
date so that no records are missed?

If the time is included it will work fine.

But remember that the End_Date needs to encompass the full days time also.

So entering 5/27/09 as End_Date will only include yesterdays fill-ups and
not today's as it does not have time included.

:

This looks like it might work. However has the unlikely but possible problem
of what if they fill up twice in one day and that happens to be the StartDate?

:

Try these two queries --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage, Min(FillDate)
AS StartDate
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(FuelGallons) – IIF(FillDate = StartDate,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) – IIF(FillDate =
StartDate, FuelGallons, 0) AS MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];


:

I need some help on how to go about creating a report of miles per gallon for
each vehicle the county owns.

We have 4 road districts, so the report will be gouped by district. Each
vehicle number (EquipmentCode) is tied to a district so I can look at the
Vehicle inventory table to determine which district the vehicle belongs to.
My fuel/mileage table looks like this

EquipmentCode - text (5) - foreign key from

FillDate - Date

InventoryID - long (foreign key - to determine which tank the fuel was taken
from for inventory purposes)

Fuel- Text (will contain either "Unleaded" or "Diesel" will be entered
automatically if taken from inventory (inventoryID combo box column) or will
be selected by the user from the value list if purchased)

FuelGallons - double

EquipmentMileage - double

CostPerGal - double (will be entered automatically if taken from inventory
(inventoryID combo box column - avg of inventory cost) or will be entered by
user acutal purchase price if purchased)

InventoryDeducted - boolean

EquipmentFuelID - autonumber - primary key


What I want to do is for a specified date range I want to calculate miles
per gallon, but my dilema is that for the first record in the range, I need
to ignore the number of gallons to accurately determine MPG. Is there an
easy way to do this?

 

[SuzyQ]

This may be a really stupid question, but here goes anyway...

As far as the date goes, the time of the fill up is not recorded by person
filling up the vehicle, and the user doing data entry nor the administration
care to enter or track the time of the fill up, so I've set this up as I do
with almost all dates fields that I create as a short date. I've never had
an issue with not including the through date in data that is being collected.
Have I missed something regarding access's internal logic?

My second question is because time is not being recorded, I can use
min([mileage]) as the start record and not use start date as the start record
and accomplish the same thing. This has been giving me headache and I've
never had such a time thinking through a logic issue, but not being an expert
in Access, has been causing me some obstacles in this project. So basically
I just need to have some assurance that my though process and what access
should be able is not unreasonable. BTW I'm using Access 2000 for this
particular project.

DID NOT TEST BEFORE POSTING --

Below will include all of the end date.
WHERE FillDate Between CVDate([Enter Start]) And CVDate([Enter End])+0.9999;

I was talking about the FillDate to include time.

Below will include all of the end date.
WHERE FillDate Between [Enter Start] And [Enter End]+0.9999;

My users will not want to be responsible for entering times when entering
date ranges for the query, especially for that rare occasion when there are
multiple fill ups. How do I, for the report request form, automaticallay
tack on the time, beginning of the day for start date and end of day for end
date so that no records are missed?

:

If the time is included it will work fine.

But remember that the End_Date needs to encompass the full days time also.

So entering 5/27/09 as End_Date will only include yesterdays fill-ups and
not today's as it does not have time included.

:

This looks like it might work. However has the unlikely but possible problem
of what if they fill up twice in one day and that happens to be the StartDate?

:

Try these two queries --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage, Min(FillDate)
AS StartDate
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(FuelGallons) – IIF(FillDate = StartDate,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) – IIF(FillDate =
StartDate, FuelGallons, 0) AS MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];


:

I need some help on how to go about creating a report of miles per gallon for
each vehicle the county owns.

We have 4 road districts, so the report will be gouped by district. Each
vehicle number (EquipmentCode) is tied to a district so I can look at the
Vehicle inventory table to determine which district the vehicle belongs to.
My fuel/mileage table looks like this

EquipmentCode - text (5) - foreign key from

FillDate - Date

InventoryID - long (foreign key - to determine which tank the fuel was taken
from for inventory purposes)

Fuel- Text (will contain either "Unleaded" or "Diesel" will be entered
automatically if taken from inventory (inventoryID combo box column) or will
be selected by the user from the value list if purchased)

FuelGallons - double

EquipmentMileage - double

CostPerGal - double (will be entered automatically if taken from inventory
(inventoryID combo box column - avg of inventory cost) or will be entered by
user acutal purchase price if purchased)

InventoryDeducted - boolean

EquipmentFuelID - autonumber - primary key


What I want to do is for a specified date range I want to calculate miles
per gallon, but my dilema is that for the first record in the range, I need
to ignore the number of gallons to accurately determine MPG. Is there an
easy way to do this?

 

[SuzyQ]

To start with... THANKS for all the help you have given me. Now PLEASE help
me some more

I'm having trouble with the second query. Syntax error at the - sign.

Here is the second query as I entered it...

SELECT tblEquipmentFuel.EquipmentCode, (Sum(FuelGallons) –
IIF(EquipmentMileage = StartMileage,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) –
IIF(EquipmentMileage =
StartMileage, FuelGallons, 0) AS MPG
FROM qryFuelMaxMinMileage LEFT JOIN tblEquipmentFuel ON
qryFuelMaxMinMileaget.EquipmentCode =
tblEquipmentFuel.EquipmentCode


The error message is missing operator in expression (Sum(FuelGallons) –
IIF(EquipmentMileage = StartMileage, FuelGallons, 0)) - when I click OK it is
the - that is highlighted.

FYI: I'm not using the date range in this query because it is handled in
another query. Following is a list of queries that are being used for this
problem...

qryFuelDetail - this is the query that handles the date ranges and is used
for many reports as the gateway to other necessary queries for other reports
etc.

SELECT tblEquipmentFuel.EquipmentCode, tblEquipmentFuel.Fuel,
tblEquipmentFuel.FillDate, tblEquipmentFuel.FuelGallons,
tblEquipmentFuel.EquipmentMileage, tblEquipmentFuel.CostPerGal,
tblEquipmentFuel.InventoryID
FROM tblEquipmentFuel
WHERE (((tblEquipmentFuel.FillDate) Between
[forms]![frmReports]![txtFromDate] And [forms]![frmReports]![txtToDate]));


qryFuelMaxMinMileage to get the minimum mileage - I'm also getting min start
and max start and mileage as well - just for kicks...(Possible future use)
Basically your report start query

SELECT qryFuelDetail.EquipmentCode, Min(qryFuelDetail.FillDate) AS
StartDate, Max(qryFuelDetail.FillDate) AS EndDate,
Min(qryFuelDetail.EquipmentMileage) AS StartMileage,
Max(qryFuelDetail.EquipmentMileage) AS EndMileage
FROM qryFuelDetail
GROUP BY qryFuelDetail.EquipmentCode;

AND HERE your second query is giving me issues that I can't seem to figure
out.

DID NOT TEST BEFORE POSTING --

Below will include all of the end date.
WHERE FillDate Between CVDate([Enter Start]) And CVDate([Enter End])+0.9999;

I was talking about the FillDate to include time.

Below will include all of the end date.
WHERE FillDate Between [Enter Start] And [Enter End]+0.9999;

My users will not want to be responsible for entering times when entering
date ranges for the query, especially for that rare occasion when there are
multiple fill ups. How do I, for the report request form, automaticallay
tack on the time, beginning of the day for start date and end of day for end
date so that no records are missed?

:

If the time is included it will work fine.

But remember that the End_Date needs to encompass the full days time also.

So entering 5/27/09 as End_Date will only include yesterdays fill-ups and
not today's as it does not have time included.

:

This looks like it might work. However has the unlikely but possible problem
of what if they fill up twice in one day and that happens to be the StartDate?

:

Try these two queries --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage, Min(FillDate)
AS StartDate
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(FuelGallons) – IIF(FillDate = StartDate,
FuelGallons, 0)) AS FuelConsumed, (EquipmentMileage – StartMileage) AS
Mileage, (EquipmentMileage – StartMileage)/ Sum(FuelGallons) – IIF(FillDate =
StartDate, FuelGallons, 0) AS MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];


:

I need some help on how to go about creating a report of miles per gallon for
each vehicle the county owns.

We have 4 road districts, so the report will be gouped by district. Each
vehicle number (EquipmentCode) is tied to a district so I can look at the
Vehicle inventory table to determine which district the vehicle belongs to.
My fuel/mileage table looks like this

EquipmentCode - text (5) - foreign key from

FillDate - Date

InventoryID - long (foreign key - to determine which tank the fuel was taken
from for inventory purposes)

Fuel- Text (will contain either "Unleaded" or "Diesel" will be entered
automatically if taken from inventory (inventoryID combo box column) or will
be selected by the user from the value list if purchased)

FuelGallons - double

EquipmentMileage - double

CostPerGal - double (will be entered automatically if taken from inventory
(inventoryID combo box column - avg of inventory cost) or will be entered by
user acutal purchase price if purchased)

InventoryDeducted - boolean

EquipmentFuelID - autonumber - primary key


What I want to do is for a specified date range I want to calculate miles
per gallon, but my dilema is that for the first record in the range, I need
to ignore the number of gallons to accurately determine MPG. Is there an
easy way to do this?

 

[KARL DEWEY]

I'm having trouble with the second query. Syntax error at the - sign.
Try ading a set of parenthsis around the IIF statement like this -
SELECT tblEquipmentFuel.EquipmentCode, (Sum(FuelGallons) –
(IIF(EquipmentMileage = StartMileage, FuelGallons, 0))) AS FuelConsumed,
min([mileage]) as the start record and not use start date as the start record
and accomplish the same thing.
I think that is the solution for you.

 

[KARL DEWEY]

Did some rethinking, try it this way --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage,
Max(EquipmentMileage) AS EndMileage
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(IIF(EquipmentMileage > StartMileage, FuelGallons,
0)) AS FuelConsumed, (EndMileage – StartMileage) AS Mileage, ((EndMileage –
StartMileage)/(Sum(IIF(EquipmentMileage > StartMileage, FuelGallons, 0))) AS
MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

If there is a problem then test in pieces --
SELECT EquipmentCode, (Sum(IIF(EquipmentMileage > StartMileage, FuelGallons,
0)) AS FuelConsumed
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(IIF(EquipmentMileage > StartMileage, FuelGallons,
0)) AS FuelConsumed, (EndMileage – StartMileage) AS Mileage
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

Try ading a set of parenthsis around the IIF statement like this -
SELECT tblEquipmentFuel.EquipmentCode, (Sum(FuelGallons) –
(IIF(EquipmentMileage = StartMileage, FuelGallons, 0))) AS FuelConsumed,
min([mileage]) as the start record and not use start date as the start record
and accomplish the same thing.
I think that is the solution for you.

 

[SuzyQ]

Finally

My final working query looks like this

SELECT tblEquipmentFuel.EquipmentCode,
Sum(IIf([tblEquipmentFuel]![EquipmentMileage]>[qryFuelMaxMinMileage]![StartMileage],[tblEquipmentFuel]![FuelGallons],0))
AS FuelConsumed,
[qryFuelMaxMinMileage]![EndMileage]-[qryFuelMaxMinMileage]![StartMileage] AS
Mileage, [mileage]/IIf([fuelconsumed]=0,1,[fuelconsumed]) AS MPG
FROM qryFuelMaxMinMileage LEFT JOIN tblEquipmentFuel ON
qryFuelMaxMinMileage.EquipmentCode = tblEquipmentFuel.EquipmentCode
GROUP BY tblEquipmentFuel.EquipmentCode,
[qryFuelMaxMinMileage]![EndMileage]-[qryFuelMaxMinMileage]![StartMileage];

I had to add in the check for mileage = to 0 meaning that the fuel
consumption was 0 which caused an divide by 0 response "#Error" in the data.

Thanks for all your help!

Did some rethinking, try it this way --
ReportStart --
SELECT EquipmentCode, Min(EquipmentMileage) AS StartMileage,
Max(EquipmentMileage) AS EndMileage
FROM fuel_mileage
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(IIF(EquipmentMileage > StartMileage, FuelGallons,
0)) AS FuelConsumed, (EndMileage – StartMileage) AS Mileage, ((EndMileage –
StartMileage)/(Sum(IIF(EquipmentMileage > StartMileage, FuelGallons, 0))) AS
MPG
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

If there is a problem then test in pieces --
SELECT EquipmentCode, (Sum(IIF(EquipmentMileage > StartMileage, FuelGallons,
0)) AS FuelConsumed
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

SELECT EquipmentCode, (Sum(IIF(EquipmentMileage > StartMileage, FuelGallons,
0)) AS FuelConsumed, (EndMileage – StartMileage) AS Mileage
FROM ReportStart LEFT JOIN fuel_mileage ON ReportStart.EquipmentCode =
fuel_mileage.EquipmentCode
WHERE FillDate Between [Enter Start] And [Enter End];

I'm having trouble with the second query. Syntax error at the - sign.

Try ading a set of parenthsis around the IIF statement like this -
SELECT tblEquipmentFuel.EquipmentCode, (Sum(FuelGallons) –
(IIF(EquipmentMileage = StartMileage, FuelGallons, 0))) AS FuelConsumed,

My second question is because time is not being recorded, I can use

min([mileage]) as the start record and not use start date as the start record
and accomplish the same thing.
I think that is the solution for you.