Time Between the Time of the Current Record and the Next Earliest

[Steve]

I am trying to put together the SQL statement to get me the time (in
minutes)between the time entry for the current record and the time of the
most recent entry just before it. For instance, in my query results, I get
entry number 10 with a time stamp of 11:00:00 AM and in the same recordset I
have entry 11 with a time stamp of 11:15:00 AM. I want the row with entry 11
to show 00:15:00. And row 10 to show the difference between it and the most
recent entry before it, etc, etc. I've been toying with the DateDiff function
and the Max function to get the most recent time before the record under
consideration, but I can't get it to work. Any ideas?


Thanks,
Steve

 

[KARL DEWEY]

Post a SQL of a select query containing all the fields to use.

 

[Steve]

Here it is:

SELECT tblCompletedMessages.CMSID, tblCompletedMessages.DateCompleted,
tblCompletedMessages.TimeCompleted
FROM tblCompletedMessages;

I need the WHERE condition to include the criteria of DateCompleted equals
the date selected on a calendar control on my form. CMSID is the employee ID
of the person that the entry refers to. The TimeCompleted field is what I am
trying to calculate the difference upon.

Thanks,
Steve

 

[KARL DEWEY]

Try this --
SELECT tblCompletedMessages.CMSID, tblCompletedMessages.DateCompleted,
tblCompletedMessages.TimeCompleted, (SELECT TOP 1
DateDiff("n",[XX].DateCompleted, tblCompletedMessages.TimeCompleted) FROM
[tblCompletedMessages] AS [XX] INNER JOIN [tblCompletedMessages] ON
tblCompletedMessages.CMSID = [XX].CMSID WHERE [XX].DateCompleted <
tblCompletedMessages.DateCompleted ORDER BY [XX].DateCompleted DESC) AS
Time_Diff
FROM tblCompletedMessages;

 

[Steve]

Ken

Your first example worked fine. The date is from the date function but time
is from the Time function (Long Time format).

Karl

Your's didn't work. The calculated fields for the minutes between completion
were blank.

Thanks to you both. This group has been a life saver for me many times.


Steve

Or this:

PARAMETERS
Forms!YourForm!YourCalendarControl DATETIME;
SELECT CMSID, DateCompleted, TimeCompleted,
DATEDIFF("n",
(SELECT MAX(TimeCompleted)
FROM tblCompletedMessages AS CM2
WHERE CM2.TimeCompleted
< CM1.TimeCompleted),
TimeCompleted) AS TimeDifference
FROM tblCompletedMessages AS CM1
WHERE DateCompleted = Forms!YourForm!YourCalendarControl;

or if you want the time difference between rows with the same CMSID value, i.
e. since the last entry by the CMSID rather than the last entry per se:

PARAMETERS
Forms!YourForm!YourCalendarControl DATETIME;
SELECT CMSID, DateCompleted, TimeCompleted,
DATEDIFF("n",
(SELECT MAX(TimeCompleted)
FROM tblCompletedMessages AS CM2
WHERE C2.CMSID = C1.CMSID
AND CM2.TimeCompleted
< CM1.TimeCompleted),
TimeCompleted) AS TimeDifference
FROM tblCompletedMessages AS CM1
WHERE DateCompleted = Forms!YourForm!YourCalendarControl;

I've assumed that you are using the Date() and Now() functions to
automatically enter the DateCompleted and TimeCompleted values, in which case
the TimeCompleted column will also include the date, even if you are
formatting it to show the time only. If on the other hand you are either
manually entering the TimeCompleted value or are 'stripping off' the date
with TimeValue(Now()) (which in fact does not remove the date element from
the value but sets it to Access's day-zero of 30 December 1899) then you'll
need to combine both values like so when computing the difference in minutes:

PARAMETERS
Forms!YourForm!YourCalendarControl DATETIME;
SELECT CMSID, DateCompleted, TimeCompleted,
DATEDIFF("n",
(SELECT MAX(DateCompleted+TimeCompleted)
FROM tblCompletedMessages AS CM2
WHERE CM2.DateCompleted+CM2.TimeCompleted
< CM1.DateCompleted+CM1.TimeCompleted),
DateCompleted+TimeCompleted) AS TimeDifference
FROM tblCompletedMessages AS CM1
WHERE DateCompleted = Forms!YourForm!YourCalendarControl;

or:

PARAMETERS
Forms!YourForm!YourCalendarControl DATETIME;
SELECT CMSID, DateCompleted, TimeCompleted,
DATEDIFF("n",
(SELECT MAX(DateCompleted+TimeCompleted)
FROM tblCompletedMessages AS CM2
WHERE C2.CMSID = C1.CMSID
AND CM2.DateCompleted+CM2.TimeCompleted
< CM1.DateCompleted+CM1.TimeCompleted),
DateCompleted+TimeCompleted) AS TimeDifference
FROM tblCompletedMessages AS CM1
WHERE DateCompleted = Forms!YourForm!YourCalendarControl;

Ken Sheridan
Stafford, England

Here it is:

SELECT tblCompletedMessages.CMSID, tblCompletedMessages.DateCompleted,
tblCompletedMessages.TimeCompleted
FROM tblCompletedMessages;

I need the WHERE condition to include the criteria of DateCompleted equals
the date selected on a calendar control on my form. CMSID is the employee ID
of the person that the entry refers to. The TimeCompleted field is what I am
trying to calculate the difference upon.

Thanks,
Steve

Post a SQL of a select query containing all the fields to use.

[quoted text clipped - 10 lines]

Thanks,
Steve

--
Message posted via AccessMonster.com


.

 

[Steve]

I see. It pulls the null date of 12-30-1899 and the current system time. But
I usually format the time function to "long time" or "medium time" before
appending to my table. But I do see what your saying about not taking too
much for granted.

Steve

Good.

I'd forgotten about the Time function, though like the TimeValue function
that doesn't do quite what it says on the tin in fact, as there is no such
thing in Access as a time value per se, only a date/time value. To see this
enter the following in the debug window (press Ctrl-G to open it)

? Format(Time(),"dd mmmm yyyy hh:nn:ss")

and hit Enter. See what you get.

Ken Sheridan
Stafford, England

Ken

Your first example worked fine. The date is from the date function but time
is from the Time function (Long Time format).

Karl

Your's didn't work. The calculated fields for the minutes between completion
were blank.

Thanks to you both. This group has been a life saver for me many times.

Steve

Or this:

[quoted text clipped - 84 lines]

Thanks,
Steve