Query to count records within time range SQL or Access Query -

i have table looks this:

row,timestamp,id 1,2014-01-01 06:01:01,5 2,2014-01-01 06:00:03,5 3,2014-01-01 06:02:00,5 4,2014-01-01 06:02:39,5 

what want count number of records each id, don't want count records if subsequent timestamp within 30 seconds.

so in above example total count id 5 3, because wouldn't count row 2 because within 30 seconds of last timestamp.

i building microsoft access application, , using query, query can either access query or sql query. thank help.

i think query below want don't understand expected output. returns count of 4 (all rows in example) believe correct because of records @ least 30 seconds apart. no single timestamp has subsequent timestamp within 30 seconds (in time).

row 2 timestamp of '2014-01-01 06:00:03' not within 30 seconds of timestamp coming after. closest row #1 58 seconds later (58 greater 30 don't know why think should excluded (given said wanted in explanation)).

rows 1/3/4 of example data not within 30 seconds of each other.

this test of sql below said returns 4 rows (change count if want count, brought rows illustrate):

http://sqlfiddle.com/#!3/0d727/20/0

now check example added data: (i added fifth row)

http://sqlfiddle.com/#!3/aee67/1/0

insert tbl values ('2014-01-01 06:01:01',5); insert tbl values ('2014-01-01 06:00:03',5); insert tbl values ('2014-01-01 06:02:00',5); insert tbl values ('2014-01-01 06:02:39',5); insert tbl values ('2014-01-01 06:02:30',5); 

note how query result shows 3 rows. because row added (#5) within 30 seconds of row #3, #3 excluded. row #5 gets excluded because row #4 9 seconds (<=30) later it. row #4 come because no subsequent timestamp within 30 seconds (there no subsequent timestamps @ all).

query detail:

select *   tbl t  not exists  (select 1           tbl x          x.id = t.id            , x.timestamp > t.timestamp            , datediff(second, t.timestamp, x.timestamp) <= 30) 

query count id:

select id, count(*)   tbl t  not exists  (select 1           tbl x          x.id = t.id            , x.timestamp > t.timestamp            , datediff(second, t.timestamp, x.timestamp) <= 30) group id