Difference in dates/times within same column grouped by individual

[JBW]

I have looked through the various posts re: calculating difference between
times but most seem to require that the dates/times be in 2 separate columns.
I am working on a project where an individual animal is remotely detected.
What I have is an Access table is individual ID for each animal in one
column, the dates the individual was detected, and in a third column the time
of detection. I can combine the dates/times into a single column if needed.
I need a query that will recognize the first time an individual is detected
and then calculate the total number of hours, minutes, seconds between the
second detection and the first; the total time between the third and second
detection, and so on through the last date/time detected. It seems like I
need some sort of lag/loop combo but don't know how to code that. Any
suggestions?

 

[Stefan Hoffmann]

hi,

I have looked through the various posts re: calculating difference between
times but most seem to require that the dates/times be in 2 separate columns.

Not really. Take a look at the various date/time functions, e.g.

http://www.classanytime.com/mis333k/sjdatetime.html
http://msdn.microsoft.com/en-us/library/aa227472(VS.60).aspx


mfG
--> stefan <--

 

[Roger Carlson]

What you need is a "running difference" query. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"DaysBetweenQuery.mdb" which illustrates how to do this. You can find it
here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=445. See
the "BY GROUP" examples.

That will get you the difference between the two dates, but it will be a
decimal value.

To convert that to days, hours, and minutes, you need a different sample:
"ConvertDateTimeToFormattedString.mdb", which you can find here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=373. Also
"ConvertStringStuff.mdb"
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=372

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[JBW]

Hey Roger - thanks for the quick response. I almost have the code modified
to fit my data but my 'uniqueID' is text, not numeric. I have been searching
online and through some books but am not quite following your coding so
haven't figured out how to modify it. When I assign a number to my text ids,
it works. Can the code be modified to accommodate a text id?

 

[JBW]

Thank you Ken and Roger! By combining parts of the queries and functions you
both offered I was able to extract the information I needed.

I'm not familiar with Roger's query, but the data type of the AnimalID
shouldn't normally make any difference. The following query should give you
the results you want, again with the elapsed time in days as a decimal number:


SELECT [AnimalID], [DateDetected]+[TimeDetected],
(SELECT MIN([DateDetected]+[TimeDetected])
FROM [YourTable] As T2
WHERE T2.[AnimalID] = T1.[AnimalID]
AND T2.[DateDetected]+[TimeDetected] > T1.[DateDetected]+[TimeDetected])
As NextDetection,
NextDetection - [DateDetected]+[TimeDetected] AS ElapsedTime
FROM [YourTable] As T1
ORDER BY [AnimalID], [DateDetected]+[TimeDetected];

If you want to convert the decimal values into a days:hours:minutes:seconds
the following function will do it:

Public Function TimeElapsed(dblTotalTime As Double, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngDays As Long
Dim lngHours As Long
Dim strMinutesSeconds As String

' get number of days
lngDays = Int(dblTotalTime)

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")
' return total elapsed time either as total hours etc
' or as days:hours etc
If blnShowDays Then
lngHours = lngHours - (lngDays * HOURSINDAY)
TimeElapsed = lngDays & ":" & Format(lngHours, "00") &
strMinutesSeconds
Else
TimeElapsed = Format(lngHours, "00") & strMinutesSeconds
End If

End Function

By default the function shows the total hours, e.g.

TimeElapsed([ElapsedTime]) might return:

57:53:17

but by passing True as the optional blnShowDays argument:

TimeElapsed([ElapsedTime], True) it would show the days and return:

2:09:53:17

Ken Sheridan
Stafford, England

Hey Roger - thanks for the quick response. I almost have the code modified
to fit my data but my 'uniqueID' is text, not numeric. I have been searching
online and through some books but am not quite following your coding so
haven't figured out how to modify it. When I assign a number to my text ids,
it works. Can the code be modified to accommodate a text id?

What you need is a "running difference" query. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called

[quoted text clipped - 28 lines]

need some sort of lag/loop combo but don't know how to code that. Any
suggestions?