Elapsed time between two dates

[KBJ]

Hi

I've got a manual machine at work that produce about 500 row's a day
6 day's a week
It produce a table like this.
Count Instr Scale Batch Gross Date Time
5000 1 1 8500 2,2 2007-04-20 11:00:10
5001 1 1 8501 2,3 2007-04-20 11:00:50
5002 1 1 8502 2,2 2007-04-20 11:06:12
And so on

What I like to do is to determine if the time between the row's >= 3
minutes, if it is, it's stop time

and of course then summarize the stop time between two date's

 

[Michel Walsh]

*if* there is only one machine,
*if* there is only one date (ie, no 2007-04-21 data in the same table than
the one with 2007-04-20)
*if* there is no hole in the count (first column)


SELECT SUM(b.[time]-a.[time]) AS totalDownTime
FROM yourTableName AS a INNER JOIN yourTableName AS b
ON b.[count] = a.[count] + 1
WHERE b.[time]-a.[time] > #00:03:00#


should do.


The second *if* can be removed if the field [time] contains and the date and
the time.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

And by 'no hole' I meant that if the value 6008 is present, and if 6010 is
present, then 6009 is also present. I also assume its order follow the time
order.

Vanderghast, Access MVP