Find multiple records with 2 Matching Fields

[Wickerman]

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?

[Vaughan]

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.

[imoree]

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

[Wickerman]

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.

[Vaughan]

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

[comment]

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...