Dear Tom:
My apology.
In the posting, I used the term "trip" instant of "TourNumber" to save
some
typing. In the original table, it exits as "TourNumber".
From your query, my understanding is that I need tables T and T1. As
well
from
"
http://support.microsoft.com/?kbid=208953 ACC2000: How to Compare a
Field
to a Field in a Prior Record", I understand that I could compare the
fields
in a table by the table's name and its alias as per article's steps:
3. Create a new, blank query based on the MileageRecord table. Add the
Date
and Mileage fields to the query grid.
4. Click the Properties button on the toolbar to view the property
sheet,
and then select the title bar of the MileageRecord table. Set the Alias
property of the table to Mile1.
Hence, I have "table (tblTest1) alias tblTest".
For consistency with the posting, my query would then read as:
SELECT Trip, DateTime, (SELECT MAX(DateTime) FROM tblTest1 WHERE
tblTest1.Trip = tblTest.Trip AND tblTest1.DateTime < tblTest.DateTime)
AS
PreviousDateTime FROM tblTest ORDER BY Trip, DateTime
Comparing to your query as:
SELECT Trip, DateTime, (SELECT MAX(DateTime) FROM YourTable T1 WHERE
T1.Trip
= T.Trip AND T1.DateTime < T.DateTime) AS PreviousDateTime FROM
YourTable
T
ORDER BY Trip, DateTime
After modification, I pasted the query into the field row of the grid.
However, once I pasted it, I got a message: "The syntax of the subquery
in
this expression is incorrect".
Am I putting the querry in the right place? The field row?
Thank you kindly for your advice. Again, apology for my earlier lack of
clarity.
Zimme
:
Dear Zimme:
The query you posted does not have anything in it like TourNumber =
Trip.
It does not reference Trip at all. It is hard to guess where this has
come
from.
I can't even tell where "table alias tblTest" is coming from. I don't
see
that, either.
Sorry, but this just isn't coherent to me.
Tom Ellison
Dear Tom:
I created tblTest1 with DateTime. Updated DateTime with an update
query. I
used another select query with your select query code, table alias
tblTest.
But once I pasted the code, it said syntax error with sub query?
(TourNumber=Trip)
SELECT TourNumber, DateTime, (SELECT MAX(DateTime) FROM tblTest1
WHERE
tblTest1.TourNumber = tblTest.TourNumber AND tblTest1.DateTime <
tblTest.DateTime) AS PreviousDateTime FROM tblTest ORDER BY
TourNumber,
DateTime
I really like to have it resolved for knowledge purpose but for your
information at this moment I got the data out by date to excel where
I
have
the issue taken care of temporary.
Thank you kindly for your trouble.
Zimme
:
Dear Zimme:
The query I provided was based on you having combined the date and
time
into
a single datetime column. Did you do that first?
I've looked over the query and I don't see why it would be a
problem.
I
looked at it several times. It is always possible to overlook
something,
and continue to be blind to it. If that's the case, then I'm still
blind
to
it.
Did you try the whole query just as I supplied it? Could you post
here
the
whole query that failed?
Tom Ellison
Dear Tom.
When I run the query suggested, I got a message: "The syntax of
the
subquery
in this expression is incorrect".
I used an alternative query as stated below.
PreviousDateTime: (select max([datetime]) from YourTable where
YourTable
.Trip= YourTable1.Trip and YourTable .datetime <
YourTable1.datetime)
With fields trip, datetime selected, sort ascending.
I got about 100 of 65,000 rows processed and MS Access hanged
(not
responding). I supposed my query was causing too much of a memory
demand?
But I couldn't figure out the syntax error with your query? Would
you
please
review? Could this be done in VBA?
Thanks. Have a nice day.
:
Dear Zimme:
Because you have put the date and time in two separate columns,
this
is
going to be tough. I strongly recommend you combine them.
That's
what a
date/time column does.
Were this in a single column, I would use:
SELECT Trip, DateTime,
(SELECT MAX(DateTime)
FROM YourTable T1
WHERE T1.Trip = T.Trip
AND T1.DateTime < T.DateTime)
AS PreviousDateTime
FROM YourTable T
ORDER BY Trip, DateTime
The above does not give you the Time_Lapsed, but it gives you
the
previous
DateTime value. Use DateDiff() to calculate the elapsed time
between
this
and the current DateTime.
Note that, in order to give the elapsed minutes, you must
consider
both
the
Date and the Time. If the previous row is for a different date,
then
that
certainly plays a part in the result. Similarly, both the Date
and
the
Time
play a role in determining which row is the previous row for a
given
Trip.
That's why the query will be a serious mess if Date and Time are
in
separate
columns. It's easier to extract a Date from a Date/Time than it
is
to
repeatedly be putting the two together.
If you current Date column is a Date/Time datatype and the Time
value
is
as
well, and if the Date column has a constant "zero" time
component
(midnight)
then combining them may be as simple as adding them. In any
case,
I
recommend making a change in how you have this stored before
proceeding.
Tom Ellison
Hello:
I need to add a calculated field to a table containing tens of
thousands
of
records in Access. This field will contain the time lapsed by
Trip
and
by
Date as sampled below. Eventually, I will need to select the
records
with
a
given Time_Lapsed.
Date Trip Time Time_Lapsed
2005/09/01 A1 15:30
2005/09/01 A1 15:35 5
2005/09/01 A1 15:55 20
2005/09/01 A1 16:10 15
2005/09/01 A2 15:35
2005/09/01 A3 15:50 15
2005/09/01 A3 16:10 20
2005/09/01 A3 16:20 10
2005/09/01 A3 16:25 5
2005/09/02 A1 15:00
Thanks for any suggestion.
