changing a field based on numbers of dups

[crazybake]

I have a list of approximately 10,000 people. Some are part of the same family and some are not. I followed the earlier posted threads to flag the duplicates with a '0" and the unique record with a "1".. it works perfect, I was then able to modify the records so that if a duplicate was found, the unique record would add the word "family" after the lastname and the duplicates were omitted... now I need some help..

I need to have the ability to set it up so that if there is only one duplicate that the modifcation of field would be different than if there was two or more duplicates (i.e. one duplicate would use the first name of the unique record and the first name of the duplicate, plus the last name. If there were two or more duplicates, it would use lastname and "family")

thanks

[spb]

I'm not sure this will work, and I don't have the time to test, but this is an idea I had.

Make a self-join relationship based on your database's unique ID (I assume the unique ID will itself be not-so-unique, duped along with the family dupes). If you don't have a unique ID, maybe you can make one with a concatenation of the street address and city field? Or just use the street address itself as a unique-to-this-family ID. Where it will fail is if the street address is not entered identically for multiple family members, as:

FamMemberA = 123 Main Street

FamMemberB = 123 Main St.

But let's assume it will work. Make a calc field off on some working layout, call it FamMemberCount, and the calculation is the Count function of the unique ID field, but using the self-join relationship:

Count(SELF-JOIN::UniqueID)

If this works, whenever you are in a show all records situation, the field should give you a count of the number of records assigned to a given family. If you are working with a found set, I believe that Count will only work on the set, not the whole database.

Now the calc field that inserts the word "family" after the last name could be modified with a Case statement:

Case (FamMemberCount = 1, NameField,

FamMemberCount = 2, FirstName & LastName,

LastName & "family")

Reading your second paragraph, I assume that when you say "only one duplicate" you are referring to two records. The case where there is only one record is the first Case clause, where you want the name to be simply the name on the record.

In the second Case clause (where two records, or "only one duplicate" exists) I just stuck FirstName & LastName. You implied you already had a way to pluck the name from each record, so insert that calc here.

The final, default Case clause is the situation where there are more than two records. This uses LastName & "family."

Like I said, I'm hazy about whether or not the Count function will work like this, but it's worth playing around with it.

Steve Brown