Need Help querying by a variable date range

[gudisdum]

Hello all
I'm attempting to filter a table of machines and alarms to remove all the same alarms on a machine that occur within 2 minutes of each other. The basic table stucture is this.
Machine Alarm Time
1 Door Open 1/1/2006 07:00:00 AM
1 Door Open 1/1/2006 07:01:00 AM
1 Door Open 1/1/2006 07:15:00 AM
1 Process Stopped 1/3/2006 07:56:00 AM
2 Watchdog timer 1/5/2006 07:00:00 AM
2 CPU Reset 1/3/2006 07:00:00 AM

Is it possible to write a query to where it would return just the 2 Door Open rows that were within 2 minutes of eachother. I've been fighting through doing it in code to filter my results but its not not working too well.

 

[Remou]

Do you mean something like:

[tt]SELECT tblAlarm.Machine, tblAlarm.Alarm, tblAlarm.Time
FROM tblAlarm
WHERE (((tblAlarm.Time)>DateAdd("n",2,(Select Top 1 A.Time From tblAlarm A Where A.Time <tblAlarm.Time Order By A.Time Desc)))) OR (((Select Top 1 A.Time From tblAlarm A Where A.Time <tblAlarm.Time Order By A.Time Desc) Is Null))
ORDER BY tblAlarm.Time;[/tt]

 

[Golom]

If you just care about ANY two times (regardless of Machine and Alarm) then

Select A.Machine, A.Alarm, A.[Time]
     , B.Machine, B.Alarm, B.[Time]

From myTable A INNER JOIN myTable B
     ON  A.[Time]  < B.[Time]

Where DateDiff("n", A.[Time], B.[Time]) <= 2

OR

If you only want those records where Machine and Alarm are the same

Select A.Machine, A.Alarm, A.[Time], B.[Time]

From myTable A INNER JOIN myTable B
     ON  A.Machine = B.Machine
     AND A.Alarm   = B.Alarm
     AND A.[Time]  < B.[Time]

Where DateDiff("n", A.[Time], B.[Time]) <= 2