Jump to content

changing a field based on numbers of dups

This topic is 6533 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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")


Link to comment
Share on other sites

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:


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

Link to comment
Share on other sites

This topic is 6533 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.