Multiple Vehicle Mileage Calculation

In:

http://groups.google.com/group/comp....d7faa790c65db1

james.ea...@gmail.com said:

:Ok, I've answered the first half of my own question. From a MSDN
:article, I can now use the following to reference the last primary
:key's values:
:
:
:DLookUp("mileage_at_purchase", "tblGas", "[prikey]=" & [prikey]-1)

I started generalizing this problem after seeing a post at
AccessMonster from someone who was about to go back to using Excel.
The subqueries work wonderfully at providing the necessary information
but the particular subqueries needed to make it all work well caused
the grouping when it came time to put it into a report to fail because
of multilevel grouping in the subqueries. I don't want to use any Make
Table queries. I will continue to look for a way to avoid those
problems. I think I understand now why the poster resorted to the use
of DLookup as an alternative to a subquery.

James A. Fortune

re: Multiple Vehicle Mileage Calculation

James,
Can you ask your question again, I am scratching my head trying to
understand what you want to accomplish.

re: Multiple Vehicle Mileage Calculation

Pachydermitis wrote:
< James,
< Can you ask your question again, I am scratching my head trying to
< understand what you want to accomplish.
[color=blue]
>Dcbrown428 posted at accessmonster.com on 05 Dec 2005:[/color]
[color=blue]
>I'm setting up a access database to keep up with gas receipts, mileage, etc
>for company vehicles. I need to be able to calculate for each vehicle the
>number of miles traveled each month based on the odometer reading. On every
>gas receipt the odometer reading is recorded. What do I need to do so the
>program will know to take the odometer reading from the earliest entry and
>subtract it from the last entry?[/color]
[color=blue][color=green]
>>Response from Tom Ellison:
>>Dear DC:
>>
>>Write a query that returns the beginning date/mileage for the month.
>>
>>Use a correlated subquery to retrieve the ending date/mileage for the month.
>>
>>Take the difference.
>>
>>Now, this may not seem like much of an answer. If you new what a correlated
>>subquery is, you probably wouldn't have asked the question. You'd just know
>>to use one.
>>
>>However, for me to give you specific query examples, I'd have to have the
>>name of your table and columns. If you want that, post the table name and
>>some data with column headings being your column names. I can work from
>>that to try to perform what you need. I then need you to study what I've
>>written and get an idea of what it's all about.
>>
>>Tom Ellison[/color][/color]
[color=blue]
>You're right, I have no idea about the subquery, but I can learn! The table
>name is gas info. My column headings are: date, $ amount, gallons, mileage
>(this is the column where the actual odometer reading is entered). I need to
>calculate the total mileage for the month. Within my query I have already
>set up formulas to add the $ amt and gallons (that was the easy part).
>Thanks for your help![/color]
[color=blue][color=green]
>>Response from Tom Ellison is omitted.[/color][/color]
[color=blue]
>I'm sorry for leaving you with so many open variables. For the date
>parameter - we will pull the report by date parameters (between [start] and
>[end]). This way we can pull for a month, quarter or ytd for each vehicle by
>specifying the date range. I'm not sure if the subquery info you gave me
>will work with the way I have this set up, but I'm going to try just to see.
>The following is an example of what the table looks like:[/color]
[color=blue]
> * * * *[/color]
[color=blue]
>Vehicle Date Amount Mileage Gallons MPG
>3 11/1/05 32.00 15,500 15
>3 11/7/05 37.50 15,800 16.2
>3 11/12/05 34.00 16,100 15.7
>------------------------------------------------
>Totals 103.50 600 46.9 12.8[/color]
[color=blue]
> * * * *[/color]
[color=blue]
>We have 20 vehicles and all the data will be sorted by vehicle with totals
>for each vehicle. I hope this gives you a better idea.[/color]
[color=blue][color=green]
>>Response from Tom Ellison is omitted.[/color][/color]
[color=blue]
>I think we're trying to make this more complicated than it needs to be. I
>have set up several access databases, but haven't run into this type of
>calculation yet. I just thought access would be a better way of doing this
>report. Thanks for your help. I hate that I took up so much of your time.
>I'll figure it out or just leave it in an excel spreadsheet.[/color]

After writing the following information:

:StartInfo

[gas info]
GIID VehicleID ReceiptDate Amount Mileage Gallons
1 3 11/1/05 32 15500 15
2 3 11/7/05 37.5 15800 16.2
3 3 11/12/05 34 16100 15.7
4 4 11/2/05 39.04 22001 18.3
5 4 11/9/05 45.68 22555 21.2


The goal is to get MPG values for each record in a query assuming that
the report can group on the VehicleID and do some totaling. If not, we
can create other queries that can be used by the report to get the
totals using a public function in the total box's ControlSource by
passing the SQL string for the totals query to the function.

qryComputeMPG:
SELECT GIID, VehicleID, ReceiptDate, Amount, Mileage, Gallons,
((SELECT A.Mileage FROM [gas info] AS A WHERE A.VehicleID = [gas
info].vehicleID AND A.ReceiptDate IN (SELECT Max(B.ReceiptDate) FROM
[gas info] As B WHERE B.ReceiptDate < [gas info].ReceiptDate AND
B.VehicleID = [gas info].VehicleID))) AS [PrevMileage],
IIf(IsNull([PrevMileage]), Null, (Mileage - Nz([PrevMileage])) /
Gallons) AS MPG FROM [gas info] ORDER BY VehicleID, ReceiptDate;

!qryComputeMPG:
GIID VehicleID ReceiptDate Amount Mileage Gallons PrevMileage MPG
1 3 11/1/05 32 15500 15 Null Null
2 3 11/7/05 37.5 15800 16.2 15500 18.518...
3 3 11/12/05 34 16100 15.7 15800 19.108...
4 4 11/2/05 39.04 22001 18.3 Null Null
5 4 11/9/05 45.68 22555 21.2 22001 26.132...

The problem with simply excluding records for the report from
qryComputeMPG that have Null MPG values is that you lose the gallons
for the first record of the month for that vehicle if you do. Maybe
IIf() can be used in the totals to weed this case out. If another
query is required to obtain the totals:

qryTotalPerVehicle:
SELECT qryComputeMPG.VehicleID, CCur(Sum(qryComputeMPG.Amount)) AS
TotalAmount, Sum(qryComputeMPG.Gallons) AS TotalGallons, (SELECT
Avg(Val(A.MPG)) FROM qryComputeMPG As A WHERE A.VehicleID =
qryComputeMPG.VehicleID AND A.MPG IS NOT NULL) AS AvgMPG FROM
qryComputeMPG WHERE (((qryComputeMPG.ReceiptDate)>=#11/1/2005# And
(qryComputeMPG.ReceiptDate)<=#11/30/2005#)) GROUP BY
qryComputeMPG.VehicleID;

!qryTotalPerVehicle:
VehicleID TotalAmount TotalGallons AvgMPG
3 $103.50 46.9 18.8133993866
4 $84.72 39.5 26.1320754717

:EndInfo

I created a Mileage.mdb database to continue trying out these ideas. I
didn't feel that what I had come up with so far was adequate because of
the way the report errored at the grouping. Also, I didn't like the
stipulations for joining AccessMonster.com so I didn't try to answer
the question directly there.

James A. Fortune

re: Multiple Vehicle Mileage Calculation

jimfortune@compumarc.com wrote:[color=blue]
> After writing the following information:
>
> :StartInfo
>
> [gas info]
> GIID VehicleID ReceiptDate Amount Mileage Gallons
> 1 3 11/1/05 32 15500 15
> 2 3 11/7/05 37.5 15800 16.2
> 3 3 11/12/05 34 16100 15.7
> 4 4 11/2/05 39.04 22001 18.3
> 5 4 11/9/05 45.68 22555 21.2
>
>
> The goal is to get MPG values for each record in a query assuming that
> the report can group on the VehicleID and do some totaling. If not, we
> can create other queries that can be used by the report to get the
> totals using a public function in the total box's ControlSource by
> passing the SQL string for the totals query to the function.
>
> qryComputeMPG:
> SELECT GIID, VehicleID, ReceiptDate, Amount, Mileage, Gallons,
> ((SELECT A.Mileage FROM [gas info] AS A WHERE A.VehicleID = [gas
> info].vehicleID AND A.ReceiptDate IN (SELECT Max(B.ReceiptDate) FROM
> [gas info] As B WHERE B.ReceiptDate < [gas info].ReceiptDate AND
> B.VehicleID = [gas info].VehicleID))) AS [PrevMileage],
> IIf(IsNull([PrevMileage]), Null, (Mileage - Nz([PrevMileage])) /
> Gallons) AS MPG FROM [gas info] ORDER BY VehicleID, ReceiptDate;
>
> !qryComputeMPG:
> GIID VehicleID ReceiptDate Amount Mileage Gallons PrevMileage MPG
> 1 3 11/1/05 32 15500 15 Null Null
> 2 3 11/7/05 37.5 15800 16.2 15500 18.518...
> 3 3 11/12/05 34 16100 15.7 15800 19.108...
> 4 4 11/2/05 39.04 22001 18.3 Null Null
> 5 4 11/9/05 45.68 22555 21.2 22001 26.132...
>
> The problem with simply excluding records for the report from
> qryComputeMPG that have Null MPG values is that you lose the gallons
> for the first record of the month for that vehicle if you do. Maybe
> IIf() can be used in the totals to weed this case out. If another
> query is required to obtain the totals:
>
> qryTotalPerVehicle:
> SELECT qryComputeMPG.VehicleID, CCur(Sum(qryComputeMPG.Amount)) AS
> TotalAmount, Sum(qryComputeMPG.Gallons) AS TotalGallons, (SELECT
> Avg(Val(A.MPG)) FROM qryComputeMPG As A WHERE A.VehicleID =
> qryComputeMPG.VehicleID AND A.MPG IS NOT NULL) AS AvgMPG FROM
> qryComputeMPG WHERE (((qryComputeMPG.ReceiptDate)>=#11/1/2005# And
> (qryComputeMPG.ReceiptDate)<=#11/30/2005#)) GROUP BY
> qryComputeMPG.VehicleID;
>
> !qryTotalPerVehicle:
> VehicleID TotalAmount TotalGallons AvgMPG
> 3 $103.50 46.9 18.8133993866
> 4 $84.72 39.5 26.1320754717
>
> :EndInfo
>
> I created a Mileage.mdb database to continue trying out these ideas. I
> didn't feel that what I had come up with so far was adequate because of
> the way the report errored at the grouping. Also, I didn't like the
> stipulations for joining AccessMonster.com so I didn't try to answer
> the question directly there.
>
> James A. Fortune[/color]

In an unrelated post, Randy Harris said:

<You should check out the Domain Aggregate Functions - DMax, DMin,
DAvg, etc

I will use DMax or DAvg if it turns out to be absolutely necessary. It
should solve the problem though.

James A. Fortune

re: Multiple Vehicle Mileage Calculation

jimfortune@compumarc.com wrote:
[color=blue]
> In an unrelated post, Randy Harris said:
>
> <You should check out the Domain Aggregate Functions - DMax, DMin,
> DAvg, etc
>
> I will use DMax or DAvg if it turns out to be absolutely necessary. It
> should solve the problem though.
>
> James A. Fortune[/color]

That idea didn't even work. After trying nested queries and different
GROUP BY combinations I tried:

SELECT VehicleID, ReceiptDate, Amount, Mileage, Gallons, (SELECT
A.Mileage FROM [gas info] AS A WHERE A.ReceiptDate =
DMax('[ReceiptDate]', '[gas info]', '[ReceiptDate] < #' & [gas
info].ReceiptDate & '# AND VehicleID = ' & [gas info].VehicleID) AND
A.VehicleID = [gas info].VehicleID) AS PrevMileage,
IIf(IsNull([PrevMileage]),Null,CDbl((Mileage-Nz([PrevMileage]))/Gallons))
AS MPG FROM [gas info] ORDER BY VehicleID, ReceiptDate;

I still got the error message:

Multi-level GROUP BY clause is not allowed in a subquery.

when trying to use sorting and grouping within the report on VehicleID.
It may have to do with PrevMileage being used later in an expression
rather than from using an extra subquery. I'll keep experimenting.

James A. Fortune

re: Multiple Vehicle Mileage Calculation

Hi James,
Your explanation was good.
A couple things:
Simplified your second qry considerable by removing the nested qry in
it.
Beware of averaging an average - your numbers will be skewed.
This qry has no iifs in it, but you can add one if you want to prevent
division by 0. This ignores the missing mileage by multiplying it by
0.
Hope this helps!

Result set looks like
VehicleID DateRange Amount Miles Gallons MPG
3 11/1/2005-11/12/2005 103.5 600 46.9 18.8087774294671
4 11/2/2005-11/9/2005 84.72 554 39.5 26.1320754716981

SELECT qryComputeMPG.VehicleID, Min([ReceiptDate]) & "-" &
Max([ReceiptDate]) AS DateRange, Sum(qryComputeMPG.Amount) AS Amount,
Sum([Mileage]-nz([PrevMileage],[Mileage])) AS Miles,
Sum(qryComputeMPG.Gallons) AS Gallons,
Sum([Mileage]-nz([PrevMileage],[Mileage]))/Sum([qryComputeMPG].[gallons]*(-Not
IsNull([PrevMileage]))) AS MPG
FROM qryComputeMPG
GROUP BY qryComputeMPG.VehicleID

re: Multiple Vehicle Mileage Calculation

Pachydermitis wrote:[color=blue]
> Hi James,
> Your explanation was good.
> A couple things:
> Simplified your second qry considerable by removing the nested qry in
> it.
> Beware of averaging an average - your numbers will be skewed.
> This qry has no iifs in it, but you can add one if you want to prevent
> division by 0. This ignores the missing mileage by multiplying it by
> 0.
> Hope this helps!
>
> Result set looks like
> VehicleID DateRange Amount Miles Gallons MPG
> 3 11/1/2005-11/12/2005 103.5 600 46.9 18.8087774294671
> 4 11/2/2005-11/9/2005 84.72 554 39.5 26.1320754716981
>
> SELECT qryComputeMPG.VehicleID, Min([ReceiptDate]) & "-" &
> Max([ReceiptDate]) AS DateRange, Sum(qryComputeMPG.Amount) AS Amount,
> Sum([Mileage]-nz([PrevMileage],[Mileage])) AS Miles,
> Sum(qryComputeMPG.Gallons) AS Gallons,
> Sum([Mileage]-nz([PrevMileage],[Mileage]))/Sum([qryComputeMPG].[gallons]*(-Not
> IsNull([PrevMileage]))) AS MPG
> FROM qryComputeMPG
> GROUP BY qryComputeMPG.VehicleID[/color]

I'll consider the problem solved now since I don't know what the OP
wants to show. Note though that the OP was concerned about returning
only the records for a particular month. The Avg I showed was only an
example of something that could be done. Your solution keyed on
grouping by the VehicleID which may be fine depending on whether or not
more detailed information is required. As you point out, the nested
query is only required for more detailed information for a given
VehicleID. Thanks for posting your SQL. I tried it in a report and
had no error when grouping by VehicleID with it.

James A. Fortune
CDMAPoster@FortuneJames.com