Checking duplicates (1 Viewer)

GingGangGoo

Registered User.
Local time
Today, 00:00
Joined
Dec 14, 2010
Messages
121
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

Super Moderator
Staff member
Local time
Today, 03:00
Joined
Jan 23, 2006
Messages
15,364
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

Banishment Pending
Local time
Today, 02:00
Joined
May 11, 2011
Messages
11,613
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

Registered User.
Local time
Today, 00:00
Joined
Dec 14, 2010
Messages
121
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

Super Moderator
Staff member
Local time
Today, 03:00
Joined
Jan 23, 2006
Messages
15,364
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 ).
 

Attachments

  • Sample_UniqueCompositeIndex.jpg
    Sample_UniqueCompositeIndex.jpg
    98.3 KB · Views: 243

GingGangGoo

Registered User.
Local time
Today, 00:00
Joined
Dec 14, 2010
Messages
121
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.
 

Attachments

  • Capture.JPG
    Capture.JPG
    31.7 KB · Views: 243

jdraw

Super Moderator
Staff member
Local time
Today, 03:00
Joined
Jan 23, 2006
Messages
15,364
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

Registered User.
Local time
Today, 00:00
Joined
Dec 14, 2010
Messages
121
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

Super Moderator
Staff member
Local time
Today, 03:00
Joined
Jan 23, 2006
Messages
15,364
interesting. I missed the web part in the forum.
 

GingGangGoo

Registered User.
Local time
Today, 00:00
Joined
Dec 14, 2010
Messages
121
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

Registered User.
Local time
Today, 01:00
Joined
Apr 9, 2016
Messages
10
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

Registered User.
Local time
Today, 03:00
Joined
Sep 25, 2009
Messages
10
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.
 

Users who are viewing this thread

Top Bottom