Checking duplicates

[GingGangGoo]

In my contacts table, I have fields LastName, FirstName, and FullName. FullName is set to no duplicates, but I am wondering if, in the view where we enter the contact initially, I can set a macro on the after update of the LastName field that would check if a record containing both FirstName and LastName already exists. The problem is that someone can enter a duplicate by slightly misspelling the last name.

 

[jdraw]

It would not be in the after update --that is too late.

In your contacts table put a unique composite index on
FirstName + LastName

Good luck.

 

[plog]

First, you shouldn't have a field called 'FullName'--You don't store redundant/calculated data. You have the first name, you have the last name, you do not need this field.

Second, you can never out-think unthinking users. They will find a way to break your database. The best you can do is think of ways they are going to do that and hopefully cut them off at the pass and then set up some sort of system to catch all the errors after they inevitably make them.

So, I would do what jdraw suggests and make a composite key on your 2 name fields. Also, I would make an interface for users that forces them to first search the database for the users they want to enter and only if they explicity say the user is not there, let them add a new user. Finally, I would set up a back-end query to go looking for duplicates that aren't exact duplicates (Robert Smith, Bob Smith, Robert Smiht, etc.). Then run that query every so often and clean up your data when it finds a dupe.

 

[GingGangGoo]

It would not be in the after update --that is too late.

In your contacts table put a unique composite index on
FirstName + LastName

Good luck.

When I did this, I run into this problem:

Enter in db
FirstName - John
LastName - Smith

Enter in db
FirstName - John
LastName - White

Not allowed because first name is a duplicate.

 

[jdraw]

No, you have not set up the composite index correctly.

You need both FirstName and LastName in the index.

Here is an example from one of my tables. In the attached jpg, notice that when looking at Indexes, the unique composiite index is name UnQ. It is made up(composed) of Tutorid and CourseId. That means that you can not have 2 records with the same TutorId and CourseID. If you try to enter a duplicate, you will get an error (3022 ).

 

[GingGangGoo]

No, you have not set up the composite index correctly.

You need both FirstName and LastName in the index.

Here is an example from one of my tables. In the attached jpg, notice that when looking at Indexes, the unique composiite index is name UnQ. It is made up(composed) of Tutorid and CourseId. That means that you can not have 2 records with the same TutorId and CourseID. If you try to enter a duplicate, you will get an error (3022 ).

Hmm, I do have both included. Here is a snapshot.

 

[jdraw]

Post a copy of the database. Remove anything confidential. Just need enough data to highlight the problem.
Do a compact and repair, then zip and post.

 

[GingGangGoo]

Post a copy of the database. Remove anything confidential. Just need enough data to highlight the problem.
Do a compact and repair, then zip and post.

I may have to come back to this later, when I am not pressed for time. I have learned that it is hard to strip, copy and post a web database.

Thanks though

 

[jdraw]

interesting. I missed the web part in the forum.

 

[GingGangGoo]

interesting. I missed the web part in the forum.

Yeah, it's not super obvious on the forum page, and I because I was in that section of the forum, I didn't think to mention it up-top, sorry for the confusion.

 

[DonLeverton]

Seeing that it seems that we are talking about an Access Desktop Database here, I think that my approach to this would be to try to do it in VBA.
Using the BeforeUpdate event, you could test for similar values, display a MsgBox with what was found, and ask the user if they would like to proceed or cancel.
If the choice is "OK", proceed with the addition of the new record.
If the choice is "Cancel", then cancel the record addition, and make the "found record" the current record.

 

[seguinsoftware]

I search the table for a combo of last name + first 3 letters of first name. Then I display a dialog with the choices found (if any) and let the user choose one of those to edit or to continue the add.