Pachydermitis wrote:
< James,
< Can you ask your question again, I am scratching my head trying to
< understand what you want to accomplish.
Dcbrown428 posted at accessmonster.c om on 05 Dec 2005:
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?
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
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!
Response from Tom Ellison is omitted.
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:
* * * *
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
* * * *
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.
Response from Tom Ellison is omitted.
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.
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.ReceiptDa te) 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:
qryTotalPerVehi cle:
SELECT qryComputeMPG.V ehicleID, CCur(Sum(qryCom puteMPG.Amount) ) AS
TotalAmount, Sum(qryComputeM PG.Gallons) AS TotalGallons, (SELECT
Avg(Val(A.MPG)) FROM qryComputeMPG As A WHERE A.VehicleID =
qryComputeMPG.V ehicleID AND A.MPG IS NOT NULL) AS AvgMPG FROM
qryComputeMPG WHERE (((qryComputeMP G.ReceiptDate)> =#11/1/2005# And
(qryComputeMPG. ReceiptDate)<=# 11/30/2005#)) GROUP BY
qryComputeMPG.V ehicleID;
!qryTotalPerVeh icle:
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.c om so I didn't try to answer
the question directly there.
James A. Fortune
