>>>>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.
>>>
>>>I believe that the ultimate solution will involve several generations of sub tables and complex, multi field indexes.
>>>
>>>Thanks again for your input.
>>
>>Erik,
>>
>>It is perfectly valid to join a table to itself (though I may have the syntax wrong).
>>
>>I hope you find what you are looking for.
>
>Yeah, I join tables to themselves regularly (this is called a unary relationship), (and I think I included the correct syntax in my response), but you can't do it twice, on two different fields, in the same query. The multiple joins are what creates the circular relatonship, just like when you join two different tables on two different fields.
>Thanks again for your suggestion.

Erik,

I just tried the following example. It works.

SELECT Test.name, Test.city;
FROM test, test Test_a;
WHERE Test_a.id <> Test.id;
AND (Test.name = test_a.name;
AND Test.city = test_a.city)

TEST.DBF has the follow data

ID NAME CITY
1 JIM VANCOUVER
2 JIM VANCOUVER
3 JIM SEATTLE
4 BILL VANCOUVER
5 JANE TORONTO

The result is two records

JIM VANCOUVER
JIM VANCOUVER

Since both of Jim's records match the other.

You can join a table using multiple fields and it is valid, so I believe that you are incorrect in saying that this is a circular relationship.

Even if this isn't the solution to your problem, I still think this is a valid join and serves the purpose of finding duplicate name/city with a different unique id.

I would like to hear from Dorris, Chuck, Mark and Barbara (or others) on this SQL question.