Find duplicates within a time range

[Melimolo]

I'm trying to write a query that will find all duplicate records that have the same time, within a maximum 10 minute time frame. For example, 9:43, 9:50 and 9:53 would be duplicate records. Then again, 9:58 and 10:08 would also be duplicate records.

I have absolutely no clue on how to write this. My time format was Long, I changed it to Short time to see if it would help. I can change it back to Long if needed.

I also have three other fields that need to be the exact same in each record to make it a duplicate. That's not hard to do. It's just that time frame that I don't know how to write!

Please help!
Meli

 

[3dman]

Hi Meli,

Are are the time ranges separated into different fields, e.g. Field 1 contains 9:43, Field 2 contains 9:50 etc.?

Also change the data type to text.

David,

 

[Melimolo]

My column with the times is called TAC. (I don't know what the abbreviation is for!) Each row has a different time. It was HH:MM:SS. I shortened it to HH:MM because I had read that it would round up the minutes. I don't know if it actually helps.

 

[3dman]

Just for now try doing this:

For the TAC column, do a Trim function to get rid of those ":", then create a primary key and a foreign key(primary key ID -1) column, self join the two columns, and do the calculations.

Example:

[TAC]
9:43
9:50
9:53

[TAC_TRIM]
943
950
953


[autonumber]
1
2
3

[autonumber_2]
0
1
2

If all goes well the next steps should be easy...hopefully.