Start Time, Stop Time

[cheshire_smile]

I have a table with a stataus of a terminal collected every minute (time collection can be changed to other time spans). I need a VBA script that will collect the time stamp stamps based on their status or change in status.

Sample Table:

Terminal_ID--Time_Stamp-------Status
2-------6/19/2011 00:01:00-------up
2-------6/19/2011 00:02:00-------up
2-------6/19/2011 00:03:00-------Down
2-------6/19/2011 00:04:00-------Down
2-------6/19/2011 00:05:00-------Down
2-------6/19/2011 00:06:00-------Down
2-------6/19/2011 00:07:00-------Up
2-------6/19/2011 00:08:00-------Up
2-------6/19/2011 00:09:00-------Up
2-------6/19/2011 00:10:00-------Down
2-------6/19/2011 00:11:00-------Up
2-------6/19/2011 00:12:00-------Up
2-------6/19/2011 00:13:00-------Up
2-------6/19/2011 00:14:00-------Up
2-------6/19/2011 00:15:00-------Up

If the status changes from Up one minute to Down the next, it records the time stamp as Start time or Down Time, and if it changes back to up, it records it as stop time or Up Time.

The results should looke like this:

Terminal_ID--Down_Time---------Up_Time
2-------6/19/2011 00:03:00--6/19/2011 00:07:00
2-------6/19/2011 00:10:00--6/19/2011 00:11:00

I've Tried doing this with SQL but I think this is one of those times where VBA would be more efficient at this.

 

[spikepl]

u just got a db in your other post on this

 

[spikepl]

and speaking of which, it is not a good idea to double post. The least you could do is to put a link from the previous post, so that any efforts are not spread all over the place

 

[cheshire_smile]

http://www.access-programmers.co.uk/forums/showthread.php?t=212296