>>>>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

Barbara, this syntax did exactly what I needed. Funny- I'm almost positive that that was the first thing I tried... you must have a special SQL touch that I haven't picked up yet :-) Thanks a ton.

Erik