Calculated fields between records in a dynaset

[Bob J]

My database has records which include dates, vehicle
numbers, and a starting and finishing odometer reading.

I want to run a query which will give me the mathematical
difference between the current record beginning odometer
reading and the previously entered day ending odometer
reading.

Here is an example of who the dynaset looks now.

Vehicle Date beginning ending

203221 9/10/2003 1,718.00 1,787.00
203221 9/11/2003 1,787.00 1,994.00
203221 9/15/2003 2,353.00 2,565.00

I want a column at the end which will give the difference
between the current day beginning and previous day ending

Can this be done with "built in" functions?

I have no experince with SQL.

 

[GreySky]

Can this be done with "built in" functions?

No.

I have no experince with SQL.

This tells me you don't care to learn SQL. Therefore, my
explanation on how to perform this will simply fly over
your head and otherwise be a waste my time.

David Atkins, MCP

 

[GreySky]

Please excuse the lack of helpfulness in my previous
post. I am easily annoyed by those who appear unwilling
to learn.

There are *many* ways of solving this, each with their
pros and cons.

--The *simple* solution is to use a correlated subquery to
return the previous result from which to do the
calculation. Unfortunately, this will render your
recordset non-updatable, and is very slow in large
recordsets.

For Example. Air query (not tested):
SELECT Vehicle, [Date], Beginning, Ending,
(SELECT Max([Ending]) FROM tblVehicle AS VS
WHERE Vehicle=V.Vehicle AND Date < V.DATE) AS Mileage
FROM tblVehicles AS V

--There is a *simple* version that uses Dlookup() to
maintain your updatability.

--There's a form-based version that dynamically calculates
this. I call this a non-inline function.

Simply put, I would need to know how you intend on using
this before I could suggest the proper solution.


David Atkins, MCP

 

[Sirocco]

Nice problem, but this sounds like a job for VB. I've seen 2 other
examples of this exact kind of problem recently, and both use VB in the
solution. The program logic is extremely simple - what the code would do is
read a record, store the ending value, read the next record, then subtract
the 2 numbers. This would be placed in a loop that would repeat for each
vehicle. Unfortunately, SQL doesn't lend itself to this problem. For a
programmer this would be simple, but for someone who who has no experience
with SQL (nor presumably other programming) this would be a remarkable
challenge.

 

[Sirocco]

Dear Mr. GreySky, you are certainly a pessimist (GreySky indeed), since
there is no reason to conclude that the original poster of this thread is
unwilling to learn SQL. I'm sure Mr. BlueSky's opinion is more
optimistic.

 

[Dale Fye]

I would agree with Dave, that it really depends on what you want to
do? Are you trying to display a list that contains a single vehicle,
and all of the dates, or a single date and all of the vehicles, or a
combination of the above.

I would approach this problem with two queries. The first one
(qry_PreviousDate) would identify the previous date for each
vehicle/date combination. Because this uses a '>' in the join, you
will not be able to view this part of the query in design view.

SELECT T1.Vehicle, T1.Date, MAX(T2.Date) as PreviousDate
FROM yourTable T1
LEFT JOIN YourTable T2
ON T1.VehicleID = T2.VehicleID
AND T1.Date > T2.Date
GROUP BY T1.VehicleID, T1.Date

Next, create a new query. Add the your table to the query grid twice
and add the previously saved query also.

Link the first version of yourTable to the query on VehicleID and
Date, then link the query to the second version of your table on
vehicleID and PreviousDate to Date. Add the appropriate fields to
your query grid, including [beginning] from the first table, then add
the [Ending] field from the second instance of your table, subtact
[beginning] from [ending] to get the difference.

--
HTH

Dale Fye


Nice problem, but this sounds like a job for VB. I've seen 2 other
examples of this exact kind of problem recently, and both use VB in
the
solution. The program logic is extremely simple - what the code would
do is
read a record, store the ending value, read the next record, then
subtract
the 2 numbers. This would be placed in a loop that would repeat for
each
vehicle. Unfortunately, SQL doesn't lend itself to this problem.
For a
programmer this would be simple, but for someone who who has no
experience
with SQL (nor presumably other programming) this would be a remarkable
challenge.

 

[Bob J]

I would love to learn SQL and I would love to learn VB

I am the Controller of a mid sized distributor and I get
called upon to assist in the IS functions.

If I could learn SQL and VB quickly I would do so. I
have taken many computer training courses but I do not
get as much out of them as I hoped to. I use the text
manuals, on-line help refernces and ask anyone I can.

e-mail me a good trainer and I will glady learn.

 

[Sirocco]

A good way to learn SQL is to look at and design queries in Access. In
it's simplest form, all you're doing with SQL is specifying which records in
a larger set of records you want to see. Most SQL instructors get carried
away with themselves and immediately get into the advanced technical
concepts and syntax while completely overlooking the more abstract but
fundamental components of the "relational model" , an understanding of which
is critical to understanding SQL and working with (relational) databases
like Access or Oracle.