querying based on interval between datetime

[tonyblack]

I have a table with the following 4 fields:

DeviceID (number)
LabID (number)
LabSer (number)
Transtime (DateTime - ##/##/#### ##:##:##)

I need to create a query that will identify table records that are duplicates for the first three fields, the date part of Transtime is a duplicate and are within 2 minutes of each other for the time part of Transtime.
I have used the duplicate query wizard to accomplish the first part but I can't find a way of linking the records by time intervals.
Any ideas would be gratefuly recieved.

Tony Black

 

[MichaelRed]

In the simplistic sense, you just need to use 'Between' for the two times. Since the two times are in seperate records, it gets to be less than simplistic.

While there are more sophisticated approaches, it may be useful/instructive to do the brute force approach:

1.[tab]Create a Make Table query ("qryMkDiff"?) which includes all four of the fields and a (new) fifth one ("DeltIntv"?). Order by the first four (e.g. the existing/original) fields, but make the new field just be a const (9999?).

2.[tab]Create a small procedure which loops through the NEW table and calcualtes the New field. It should be LEFT at the 'large value' (9999?) if the first four fields are not equal, but set to the DateDiff of the current field and the previous field if the first four fields are equal.

3.[tab]Create a select query bas on the new table with the calculated value ("DeltIntv&quot where the value is less than or equal to the interval (2 Minutes -or better- 120 Seconds). This should be the set which you are interested in.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places