finding results in a table where time is within 1 second of other rows

[Help.Chris]

Hi All,

I am stuck with this problem, and could really do with some help. Thanks in advance for any!!!

I have a table with the sample data below

CLI StartTime EndTime
0161292 01/08/2003 13:56:36 01/08/2003 13:56:36
0161292 01/08/2003 13:45:31 01/08/2003 13:45:31
0190284 01/08/2003 17:30:50 01/08/2003 17:30:50
0190284 01/08/2003 17:23:25 01/08/2003 17:23:25
0190284 01/08/2003 17:23:24 01/08/2003 17:23:24

I need to find records where the start time is within 1 second of another record in the table. ie. from the above results i would want

0190284 01/08/2003 17:23:25 01/08/2003 17:23:25
0190284 01/08/2003 17:23:24 01/08/2003 17:23:24

returning. I am at a loss how to do this and if it is possible.

Please help

Thanks

Chris

 

[Shadez]

You will have to make a ado/dao record set then row process using

DateDiff

in VBA

 

[dcx693]

Help.Chris, you can avoid using VBA for this by joining the table to a copy of itself, then using the DateDiff function.

Some explanation of the technique can be found here: Calculate difference of values

It looks like the Start and End times of all your records are the same. Is that right?

 

[Help.Chris]

Will give the linked queries a go!!! thanks

Yes the start and end date are generally the same but not always, I will give the linking a table to itseld a go. Thanks for the advise, much appreciated!!! I am more comfortable with queries than with recordsets, but thanks for the help.

Chris