>>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.
>
>
>Sorry the sql select should be based off of last name
>
> SELECT *, COUNT(*) AS NUMTIMES FROM membership GROUP BY LNAME,
> CITY HAVING COUNT(*) > 1
>
> Charles
Thanks Charles...
this query doesn't take into account that the only real possible duplicate records have one with a system assigned ssn. I already know that there are no duplicate ssns, this is system enforced. The query you suggested would return a HUGE number of records, equal to the number of members who live in a city with another member of the same last name.
Erik Moore
Clientelligence