remove duplicates between time interval

[mfaqueiroz]

Hi!
I have two tables:
1) Table 1 with Off Events
2)Table 2 with Alarms events
The alarms events could have different nomenclatures:
-Alarm
-Alarm x
-Alarm y
-Alarm z
I need to analyse the alarms 4 seconds before my Off event. In this analysis is important delete the repeated alarms and only keep the last ones. As the following image:


For last I should calculate the difference between the first alarm and the off event.

I've written the following code, I'm stuck in removing duplicates and the virtual query is putting the program so slow! :/ do you have other ideia how can i do that?





Sub Alarm()
Dim.....



Set dbs = DBEngine(0)(0)
Set OFF = dbs.OpenRecordset("Table1", DB_OPEN_DYNASET) 'Abre a Query de input, onde estao todos os delisgado
Set ALARM = dbs.OpenRecordset("Table2", DB_OPEN_DYNASET) 'Abre a Query de input, onde estao todos os delisgado

OFF.MoveFirst
Do While Not OFF.EOF

ClasDesc = OFF.Fields(17)
TIMEOFF = OFF.Fields(16)



Difference4seconds = DateAdd("s", -4, CDate(Format(TimeOFF, "dd-mm-yyyy hh:mm:ss")))

DateDif = Mid(Difference4seconds, 7, 4) & "-" & Mid(Difference4seconds, 4, 2) & "-" & Mid(Difference4seconds, 1, 2) & " " & TimeValue(Difference4seconds)
TimeOFF1 = Mid(OFF.Fields(2).Value, 7, 4) & "-" & Mid(OFF.Fields(2).Value, 4, 2) & "-" & Mid(OFF.Fields(2).Value, 1, 2) & " " & TimeValue(OFF.Fields(2).Value)
Set AlarmsQuery = dbs.OpenRecordset("SELECT Alarm.ID, Alarm.[My date], Alarm.[AlarmStatus], from Alarm WHERE (((Alarm.[ My date])< #" & TimeOFF1 & "#) AND ((Alarm.[My date])>= #" & DateDif& "#) ORDER BY Alarm.[My Date];", DB_OPEN_DYNASET)

'Note: After that how can i delete the duplicates?!


AlarmsQuery .MoveFirst

Do While Not AlarmsQuery .EOF

TimeAlarm = AlarmsQuery .Fields(1)
TIMEOFFAlarmsQuery = AlarmsQuery .Fields(8)


If DataA >= Difference4seconds Then

DifBetweenAlarmOFF = Abs((DateDiff("s", TimeOFF, TimeAlarm)


OFF.Edit
OFF.Fields(18).Value = DifBetweenAlarmOFF
OFF.Update

Else
AlarmsQuery.MoveNext
End If


Loop


OFF.MoveNext
Loop


End Sub

 

[Guus2005]

use a group by clause and group on Max(My Date) and max(Alarm.Alarmstatus)

Your duplicates will vanish as long as you leave the Alarm.Id out of it.
If you need the Alarm.Id field, use the above query as a subquery to retrieve the additional fields.

BTW, use the code tags to display your code in a nice way.

HTH