>>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.
>
>Erik:
>
>I have followed this entire thread. Have you tried Barbara's solution? That should get what you need with minimal number of records in results set. The SQL joins your membership table to the subset table with the system ID records retrieves the recs with matching lastname+city with a count greater than 1.

I tried Barbara's suggestion, and it works as designed, but it came up short in one way... it doesn't return values from both tables so I can compare to see what the query thought might be duplicates. So I modified it as follows:

SELECT memb.fname as mfname, memb.lname as mlname, memb.nickname as mnickname,;
memb.address1 as maddress1, memb.address2 as maddress2, memb.tel as mtel,;
sysssn.fname as sfname, sysssn.lname as slname, sysssn.nickname as snickname,;
sysssn.address1 as saddress1, sysssn.address2 as saddress2, sysssn.tel as stel,;
count(*) as NumTimes, memb.lname+memb.city as LastCity from Memb, sysssn;
where memb.lname+memb.city in (Select sysssn.lname+city as LastCity from Sysssn);
group by memb.lname, memb.city having NumTimes > 1

What I got was a couple of hundred records that listed names that did not match. I think I understand why this didn't work; the tables aren't _really_ joined by the WHERE IN clause. So what I need is a way to join the tables, to get the records from both. I feel that Barbara's suggestion put me on the right track, I just haven't been able to pin it down yet. I think that I can create more fields that are concatenated city and lname and then set relations to those fields... I'll let you know how I get it worked out