Avoid duplication without using Indexed fields and 'no duplication'

Question

Hi there,

I'm looking for advice as to the correct method for form design to prompt the user entering data to avoid duplicated records.

For example: The user enters profile information in the fields firstName surName Its highly possible for numerous profiles to have the same name (e.g John Smith, so indexing fields isn't possible to avoid duplicates). Its possible for the user to enter the same record twice if they are not prompt/warned not to do so.

What is considered the best method to prompt users to avoid duplicate entries and highlight that a record 'may' already exist?

Thanks in advance for any advice.

Answer 1

I find that selecting matching records from the table in the before update event is useful, a list of possible matches with an additional identifier, such as address or date of birth can be shown to the user who can then make the decision as to whether this is a new or existing John Smith. You might also like to consider soundex, for close matches.

Answer 2

My approach for de-duping people during data entry is to use an unbound form that collects the required fields, then checks the existing data and presents a list of possible matches, sorted in descending order of closeness of matches. I use Soundex() and Soundex2() on the names an various substrings for determining the rank.

Here's a sample Add Customer form from one of my client's apps:

The dots at the left show the level of match, with red for exact (or near-exact -- I can't quite recall the exact formula), orange for close match, then yellow and going to green for very remote matches. The closest matches are at the top of the list.

The point is that the duplicate checking is done here, when the ADD button is clicked, and then the user has to determine if any of the possible matches are a real duplicate, and then add or not.

In other words, I depend on human intelligence to avoid creating duplicates, but they can create them if they want to. But I make it as easy as possible to avoid doing so.