difference in row values

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi there...

I'm running Access 2000, and I have a table with this data:


id time
1 1/3/2006 2:38:33 PM
2 1/3/2006 2:54:45 PM
3 1/3/2006 3:03:23 PM


What I need to do is figure out the difference (in minutes) between the
field 'time' values in row 1 and row 2, row 2 and row 3, etc., but I'm
not sure how to do this. 'id' is the Primary Key. Any suggestions?


Thanks!
Mark
 
If the Ids are strictly ascending (even if they have gaps in the numbering),
you can create a query like the following to retrieve the time value from
each row and the previous row, and use the DateDiff function to calculate
the difference between them:

SELECT A.Id,
A.TimeField As CurrentTime,
Max(B.TimeField) As PreviousTime,
DateDiff("s", PreviousTime, CurrentTime) As DiffInSecs
FROM TimeValues AS A
LEFT JOIN TimeValues AS B
ON B.Id < A.Id
GROUP BY A.Id, A.TimeField

(note that I renamed the field from Time to TimeField: Time is a reserved
word, and should never be used for your own purposes)

Running this query, I get:

Id CurrentTime PreviousTime DiffInSecs
1 2006-01-03 14:38:33
2 2006-01-03 14:54:45 2006-01-03 14:38:33 972
3 2006-01-03 15:03:23 2006-01-03 14:54:45 518
 
Back
Top