I have time-stamped data that is recorded while a piece of hardware is in operation (which is intermittent). The data looks something like this:

Time
1/2/06 1:00am
1/2/06 2:00am
1/2/06 2:30am
1/2/06 3:00am
1/4/06 9:00pm
1/4/06 10:00pm

From this data, I want to count the number of hours the unit is in operation to get this:

Hours Time
1 1/2/06 1:00am
2 1/2/06 2:00am
2 1/2/06 2:30am
3 1/2/06 3:00am
4 1/4/06 9:00pm
4 1/4/06 10:00pm

The data will always be in chronological order, but note that there are sometimes multiple entries per hour so I can't use a simple count function and it is not continuous operation so I can't use a time elapse function.

Is there a way to count this using SQL code in Access or do I have to export the data to excel to count?

Thanks.

First thing's first:

Where is the data coming from, and under what parameters? Is the equipment sending stamps when it starts and when it stops? Or intermittently based on what event? Without knowing this, you can't process your data. For example, if it sends a start time, some intermittent times, and an end time, then the equipment ran for 2 hours on 1/2/2006, not 3 hours.

Also, will it always reliably send a start and stop time?

Also, can the equipment ever run for many days at a time continously?

Your best bet is to query for the high and low on each day and then do a DateDiff(), but if it can go for weeks at a time, this will need to be tweaked.

Also, how can you tell which is a start and which is a stop time? Can you have the equipment report differently to a start time and stop time column?

HTH

mmcdonal