Records Elimination depending on Time difference

  • Thread starter John via AccessMonster.com
  • Start date
J

John via AccessMonster.com

There are two fields in a table i.e. Time, Name.
I want to eliminate records where the Time difference for any record is less
than 10 seconds. This is required to overcome the problem of duplicate
entries. Please help in this regard.
 
D

Duane Hookom

With one time field, how can there be a time difference? You might need to
provide some sample records and your desired output. Providing table and
field names helps.

Also, both Time and Name are reserved words. Time is a function name and
Name is a property of every object.
 
J

John via AccessMonster.com

For Example my table is as under:

Time_Value Customer_Name
12/16/2005 1:00:00 PM Desoza
12/16/2005 1:00:08 PM Lilly
12/16/2005 3:00:01 PM Jhonson
12/16/2005 3:30:00 PM Jun

Required Table Result

Time_Value Customer_Name
12/16/2005 3:00:01 PM Jhonson
12/16/2005 3:30:00 PM Jun

Please note the records eleminated have only time difference of less than 10
seconds.
Regards
 
D

Duane Hookom

Since you don't want to display either of the close time records, you can
use:

SELECT [is as under].*
FROM [is as under]
WHERE ((((Select COunt(*) FROM [is as under] iau WHERE iau.Time_Value <> [is
as under].Time_Value AND iau.Time_Value BETWEEN DateAdd("s",-10,[is as
under].Time_Value) AND DateAdd("s",10,[is as under].Time_Value)))=0));
 
J

John via AccessMonster.com

Thank you, It was a great help.

Duane said:
Since you don't want to display either of the close time records, you can
use:

SELECT [is as under].*
FROM [is as under]
WHERE ((((Select COunt(*) FROM [is as under] iau WHERE iau.Time_Value <> [is
as under].Time_Value AND iau.Time_Value BETWEEN DateAdd("s",-10,[is as
under].Time_Value) AND DateAdd("s",10,[is as under].Time_Value)))=0));
For Example my table is as under:
Click to expand...
[quoted text clipped - 27 lines]
Click to expand...
 
Top