Dear DC:
I'm changing my approach considerably:
SELECT T1.Vehicle,
MIN([Date]) AS FirstDate, MAX([Date]) AS LastDate,
MIN(Mileage) AS BeginMiles, MAX(Mileage) AS EndMiles,
MAX(Mileage) -
(SELECT MAX(Mileage) FROM [gas info] T2
WHERE T2.Vehicle = T1.Vehicle
AND T2.[Date] < MIN(T1.Date)) AS Miles,
MIN(Mileage) AS MilesTraveled,
SUM(Amount) AS Amount, SUM(Gallons) AS Galons
FROM [gas info]
WHERE [Date] Between [start] AND [end]
GROUP BY Vehicle
Actually, given that I spent several minutes listing things I need to know,
it would be helpful if you'd read that carefully and attempt to answer each
question specifically and completely. OK?
We have not addressed the issue of the odometer turning over. Perhaps the
company doesn't keep a car past 100,000 miles. If they do, the data would
need to be entered with 100,000 or 200,000 added to the mileage when it is
missing. Otherwise, we'll need some real query smarts to detect and
overcome this. Just use an IIF on the Miles value above and add the 100,000
to that. Some odometers, however, turn over at 1,000,000 miles instead.
Perhaps you'd never keep a vehicle with so many miles, but if you do, you'd
need another test for this. When the beginning mileage is more than 900,000
then add 1,000,000 instead of 100,000.
When a vehicle first comes into service, you need a beginning record for
that. Make sure the date is put in as something long ago, like 1950
perhaps. That will represent a purchase of no gasoline and a beginning
odometer reading.
MPG calculations are simple. I've omitted that.
It is not too difficult to do this in a query, but you may want to look at
doing some of these calculations in a report. It will total things like you
want quite nicely. The only thing you'd need is the subquery, which could
simply return the mileage at the previous fill, as shown above. Let the
report do the calculations from there.
Alsays think through and look for exceptions, like the one about an odometer
turning over.
Are we getting closer?
Tom Ellison
Dcbrown428 said:
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.
Tom Ellison said:
Dear DC:
You did not show me any sample data, as I had asked. If you were to do
so,
it might avoid misunderstanding.
Another issue is the definition of a "month". By this, do you mean a
calendar month, or the period between two specified dates? Is it only to
be
the current month, based on the system date?
Do you ever have two rows in the table for the same date? If so, the
ending
milage for the month should probably be the row with the greater mileage
on
the last date entered, right? Or maybe just the maximum mileage within
the
month, since mileage only goes up, except when an odometer "turns over."
I
won't attempt to handle that for now.
How do you define the month for which you want this done? Is it based on
the computer's system date? Is is selected in a combo box? Will it be a
query parameter?
So, the subquery could be:
(SELECT MAX([mileage])
FROM [gas info] G1
WHERE Year(G1.[date]) = Year(Now())
AND Month(G1.[date]) = Month(Now())
Perhaps you want a query that gives the beginning and ending mileage for
each vehicle over a series of months. There are many possible likely
variations to what you may want. Sorry, but I've run into so many
different
needs like this that my experience tells me I really don't know a great
deal
about what you want. Care to be specific?
May I suggest that a sample of data and of the results you want would
clear
up some of this. Please also specify how you are selecting a particular
month on which to derive this, unless you want it over many months. Do
you
want all the month stored to be reported?
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!
:
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
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?
Thanks!
