Query That Compares 2 Adjacement Records

[carl]

My database looks like this:

ID Time Date Code
1 93116 20060403 UMUD06C30.00
2 93137 20060403 QAAE06C65.00
3 93141 20060403 QAAE06C65.00
4 93150 20060403 DLQD06C30.00

Can a query filter out (Exclude) Record 3 based on the logic: If Record "N"
Code is the same as Record "N+1" Code and the difference between Record N+1
Time and Record N Time is less than 5 seconds.

I think the query needs to reformat my Time data as well (Time Field)
although it is not necessary for Time to be displayed in the query result.

Thank you in advance.

 

[Michel Walsh]

Hi,




SELECT a.dateTime, a.code

FROM (myTable As a LEFT JOIN myTable As b
ON a.code=b.code AND a.DateTime > b.DateTime)
LEFT JOIN myTable As c
ON a.code = c.code AND a.DateTime>c.DateTime

GROUP BY a.dateTime, a.code, b.DateTime

HAVING Nz(b.DateTime, MAX(c.DateTime)) = MAX(c.DateTime)
AND
DateDiff( "s", Nz(b.DateTime, 6+a.DateTime), a.DateTime) >= 6




where the magical number 6 is... the first integer > 5 (occurs twice). You
can change the HAVING clause to:

HAVING b.DateTime IS NULL
OR( b.DateTime = MAX(c.DateTime) AND DateDiff("s", b.DateTime,
a.DateTime) > 5 )





The solution works without consecutive un-interrupted ID, or ID that would
not designate a uniquely determined or being associated to a strictly
growing sequence (in time).


Hoping it may help,
Vanderghast, Access MVP