I'm needing to clear out redundant records in a table where there is, say a Foreign Key field linked to 'People' and a Content field like, oh, "Activity" (Golf, Chess, etc.) The gist is the table 'tags' people with various activities, but there should be no duplicate tags -- we only need to know that Janice plays Chess *once* -- not several times.

This seems like it should be simple, but I'm not immediately seeing it -- and I'm wondering whether there's

(a) A direct way to find all such records via a Find technique that would give me a set of all records where the FK and Activity are both matching any other record. (This need just comes up in various scenarios.

( what's the most efficient way to find and delete these dupes, so that each record has a unique combination of FK/Activity. I'm thinking a self-join based on 2 relationships with the two fields matching, a Summary field to count record, and a looping script to work through all records and knock down the related Count to 1? Is that the right approach?

First, backup your database.

To prevent duplicates in the future, create a text field (I'll call it "Unique" but the name does not matter) with an auto-enter calculation:

foreignkeyfield & " " & activity

Set this field to validate unique, always, and not allow user over-ride.

Now, export the data. Delete all the records. Import the data back in again with all the auto-enter options enabled. The validation will prevent duplicates from being imported.

First, backup your database.

To prevent duplicates in the future, create a text field (I'll call it "Unique" but the name does not matter) with an auto-enter calculation:

foreignkeyfield & " " & activity

Set this field to validate unique, always, and not allow user over-ride.

Now, export the data. Delete all the records. Import the data back in again with all the auto-enter options enabled. The validation will prevent duplicates from being imported.

slick Vaughan..

Kewl technique

Ahhhhh --- nice! THis is useful in a lot of situations, because I had always been stymied in cases where I wanted to require a calc field to be Unique. Never thought of the auto-entry equivalent! Thanks a million.

I think Comment mentioned this technique w-a-y back, so I'll pass the compliments onto him (but keep 10% commission). :D

I'll pass the compliments onto him (but keep 10% commission).

If I knew that was the arrangement, I would have given you all my business...