>>I need to find potentially duplicate records in my membership database. The search needs to do the following:
>>Look at all records with a system assigned ssn (our system assigned numbers start with "000") and compare them to all other records to check for people with the same last name living in the same city. I have created a subset table containing only the records with system assigned ssns, but I am sort of scratching my head as to what to do next. The main table is unavailable to me for esclusive use... should I copy it in order to create additional indexes to search on? Is there a single SQL statement that would take care of this? Thanks in advance for any help.
>
>I think I understand your question... If you want to simply find out if the membership table has any duplicate ssns you could sql-select that counts the occurences of each ssn, then search through that cursor to find any counts over 1.
>
>Something like this...
>
>select membership.ssn, cnt(membership.ssn) as dupnum;
> from membership;
> group by membership.ssn;
> into cursor snncnts
>select ssncnts.ssn, ssncnts.dupnum
> from ssncts;
> where ssncnts.dupnum > 1

There are no duplicate ssns in the table. This is system enforced.

>If you are looking for names/city that may be duplicate with a different ssn then you could do something similar. I think this >works...
>select membership.name, membership.city, cnt(ssn) as dupcnt;
> from membership;
> group by membership.name + city;
> into cursor memcnts

>select *;
> from memcnts;
> where memcnts.dupcnt > 1

The same holds true for this query, which would only bring forth records with duplicate ssns
(BTW, I got the gist of the suggestion, but I believe that the group by clause must contain fields fro mthe select statement:
select memb.lname + memb.city as membcity, cnt(ssn) as dupcnt;
from memb;
group by membcity;
into cursor memcnts

>select membership.name;
> from membership;
> where membership.name = membership.name;
> and membership.city = membership.city;
> and membership.ssn <> membership.ssn;

Yeah- I was hoping to do something like this, but this would result in a circular relation ship.

select memb1.lname as lname1, memb2.lname as lname2 (blah blah blah)
from memb memb1, memb memb2;
where memb1.lname1 = memb2.lname2;
and memb1.city = memb2.city;
and memb1.ssn <> memb2.ssn

I believe that the ultimate solution will involve several generations of sub tables and complex, multi field indexes.

Thanks again for your input.