>>>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?
>
>No. Here's the situation. I have a table (~280,000 records) that is sort of a transacttion table that keeps track of a member's membership history. Simplified, here's the structure: jcnum (key, integer) membnum ( foreign key, integer), activity (text), jdate(date). A record might look like this:
>200101,104534,"EXPIRE", {09/01/97}. or
>200102,104534,"JOIN", {04/05/95}
>
>I want to find members who have more than one expire record, and in another query, members with more than one expire on the same date. (We have found a glitch in the program that was occasionally adding duplicate records). Thanks for your help.
>
>Erik

Erik, I'm confused by your first message (2 out of 5 matching fields) but working from your last message:

SELECT jcnum, membnum, activity, jdate, count(*) as totnum from table1 ;
WHERE activity = 'EXPIRE' INTO cursor atemp
GROUP BY membnum HAVING totnum > 1

should give you all the members with 2 or more EXPIRE records

or, for the second query

SELECT jcnum, membnum, activity, jdate, count(*) as totnum from table1 ;
WHERE activity = 'EXPIRE' INTO cursor atemp
GROUP BY membnum, jdate HAVING totnum > 1

Please ignore this if I've missed something from the earlier messages.

Barbara