Elapsed time with only 1 column

  • Thread starter Thread starter Jaimeta
  • Start date Start date
J

Jaimeta

Thanks for your help - I need module code to run this table:

Index Starttime Difference Person
1 4/5/2010 6:00:00 AM Sally
2 4/5/2010 6:30:00 AM Sally
3 4/5/2010 7:30:00 AM Sally
4 4/6/2010 11:40:00 PM Peter
5 4/6/2010 11:50:00 PM Peter

I want to put in the "Difference" column, the time in seconds between the
first record and the second. Also, I need the time to start over at 0 when
the person column changes.

For example:

Index Starttime Difference Person
1 4/5/2010 6:00:00 AM 0 Sally
2 4/5/2010 6:30:00 AM 00:30:00 Sally
3 4/5/2010 7:30:00 AM 01:00:00 Sally
4 4/6/2010 11:40:00 PM 0 Peter
5 4/6/2010 11:50:00 PM 00:10:00 Peter
 
A small amount of records can be handled with

SELECT StartTime
, DMax("StartTime","YourTable"
,"StartTime<" & Format([StartTime],"#yyyy-mm-dd hh:nn:ss#") & "Person=""" &
[Person] & """") as PriorTime
FROM YourTable

You can use the DateDiff function to calculate the number of minutes or
seconds between the two times.

If you have a large amount of records you can try a query that looks like the
following.

SELECT A.Person
, A.StartTime
, Max(B.StartTime) as PriorTime
, DateDiff("s",Max(B.StartTime),A.StartTime) as ElapsedSeconds
FROM YourTable as A LEFT JOIN YourTable As B
ON A.Person = B.Person
AND A.StartTime > B.StartTime
GROUP BY A.Person, A.StartTime


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Awesome! Spectacular! All I could find was info for 2 column tables.

So my frustration level was climbing. Thanks so much!


John Spencer said:
A small amount of records can be handled with

SELECT StartTime
, DMax("StartTime","YourTable"
,"StartTime<" & Format([StartTime],"#yyyy-mm-dd hh:nn:ss#") & "Person=""" &
[Person] & """") as PriorTime
FROM YourTable

You can use the DateDiff function to calculate the number of minutes or
seconds between the two times.

If you have a large amount of records you can try a query that looks like the
following.

SELECT A.Person
, A.StartTime
, Max(B.StartTime) as PriorTime
, DateDiff("s",Max(B.StartTime),A.StartTime) as ElapsedSeconds
FROM YourTable as A LEFT JOIN YourTable As B
ON A.Person = B.Person
AND A.StartTime > B.StartTime
GROUP BY A.Person, A.StartTime


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks for your help - I need module code to run this table:

Index Starttime Difference Person
1 4/5/2010 6:00:00 AM Sally
2 4/5/2010 6:30:00 AM Sally
3 4/5/2010 7:30:00 AM Sally
4 4/6/2010 11:40:00 PM Peter
5 4/6/2010 11:50:00 PM Peter

I want to put in the "Difference" column, the time in seconds between the
first record and the second. Also, I need the time to start over at 0 when
the person column changes.

For example:

Index Starttime Difference Person
1 4/5/2010 6:00:00 AM 0 Sally
2 4/5/2010 6:30:00 AM 00:30:00 Sally
3 4/5/2010 7:30:00 AM 01:00:00 Sally
4 4/6/2010 11:40:00 PM 0 Peter
5 4/6/2010 11:50:00 PM 00:10:00 Peter
Click to expand...
.
Click to expand...
 
I have another question called: 5 minute time increments, maybe you can help
with that question too!

John Spencer said:
A small amount of records can be handled with

SELECT StartTime
, DMax("StartTime","YourTable"
,"StartTime<" & Format([StartTime],"#yyyy-mm-dd hh:nn:ss#") & "Person=""" &
[Person] & """") as PriorTime
FROM YourTable

You can use the DateDiff function to calculate the number of minutes or
seconds between the two times.

If you have a large amount of records you can try a query that looks like the
following.

SELECT A.Person
, A.StartTime
, Max(B.StartTime) as PriorTime
, DateDiff("s",Max(B.StartTime),A.StartTime) as ElapsedSeconds
FROM YourTable as A LEFT JOIN YourTable As B
ON A.Person = B.Person
AND A.StartTime > B.StartTime
GROUP BY A.Person, A.StartTime


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks for your help - I need module code to run this table:

Index Starttime Difference Person
1 4/5/2010 6:00:00 AM Sally
2 4/5/2010 6:30:00 AM Sally
3 4/5/2010 7:30:00 AM Sally
4 4/6/2010 11:40:00 PM Peter
5 4/6/2010 11:50:00 PM Peter

I want to put in the "Difference" column, the time in seconds between the
first record and the second. Also, I need the time to start over at 0 when
the person column changes.

For example:

Index Starttime Difference Person
1 4/5/2010 6:00:00 AM 0 Sally
2 4/5/2010 6:30:00 AM 00:30:00 Sally
3 4/5/2010 7:30:00 AM 01:00:00 Sally
4 4/6/2010 11:40:00 PM 0 Peter
5 4/6/2010 11:50:00 PM 00:10:00 Peter
Click to expand...
.
Click to expand...
 
Back
Top