>>Can someone help me with the SQL to find duplicate records in a table where a duplicate is considered two out of five matching fields? I thought:
>>SELECT * ;
>>FROM table;
>>WHERE keyfield IN ;
>>(SELECT keyfield ;
>>FROM tablel ;
>>GROUP BY keyfield, field2;
>>HAVING count(*) > 1)
>>would do the trick, but it doesn't work. TIA
>>
>>Erik
>Need more info. Can field1 in one record be = field2...5 in another record?


Here is something that should work. Based on table1 having fields name a, b, c, d, e, key. The key can be filled with the recno() as long as it is unique and in this example an integer.

select t1.key + t2.key as sum, t1.key, t2.key, t1.a, t2.a, t1.b, t2.b, t1.c, t2.c, t1.d, t2.d,;
t1.e, t2.e ;
from table1 t1, table1 t2, table1 t3, table1 t4, table1 t5 ;
where t1.key <> t2.key AND (;
(t1.a = t2.a AND t1.b = t2.b) OR ;
(t1.a = t2.a AND t1.c = t2.c) OR ;
(t1.a = t2.a AND t1.d = t2.d) OR ;
(t1.a = t2.a AND t1.e = t2.e) OR ;
(t1.b = t2.b AND t1.c = t2.c) OR ;
(t1.b = t2.b AND t1.d = t2.d) OR ;
(t1.b = t2.b AND t1.e = t2.e) OR ;
(t1.c = t2.c AND t1.d = t2.d) OR ;
(t1.d = t2.d AND t1.e = t2.e)) ;
GROUP BY sum ;
INTO CURSOR QUERY

Note, this returns the key values and data values for records that have matched. The sum is used to filter out records that match as t1 and again as t2 (the key sums will be the same) Actually, now that I think of it, the sum won't work as a filter but it works as a first pass. Worst case is, you get two copies of the match with the key_a and key_b reversed. The concept, I think, is sound.