Preventing duplicate names

Topic

New Reply

I have a problem that is driving me nuts…

In my Participant table, I keep First Name and Last Name in separate fields. However, the users keep on entering duplicate names and setting each field to prevent duplicates doesn’t work because there are many same last names such as “Anderson”. I need a way to force the table to look at both fields and determine whether it is a duplicate or not.

I’ve already told users many times to double check, but they still end up adding a new record for same person that already exists in the database.

Reply | Quote

Replies

Write a function to check if the lastname and the firstname already exists in your database. In the function you can use the dCount function like this:

dim intNumber as long

intNumber = dCount(“LastName”, “YourTableName”, “Lastname=’ & trim(me![txtLastname]) & “‘ AND Firstname='” & trim(me![txtFirstname]) & “‘”)
if intNumber > 0
It exists already
Do something usefull, give a message!
endif

If you call this function on the right event of your form (on update for instance) you can take some appropriate actions.

Reply | Quote

You can set a mutli-field key, which should accomplish your design goal. In design view for the table, select both the first and last name fields, then hit the Key button. Walla!

I do have to wonder, though, if you really want this. Might you have two people with the same first and last name but are two distinct people? Lots of John Smith’s running around in the anglo-world.

Edited by Charlotte to remove excess space

Reply | Quote

Shane,

You are right, there are a lot of John Smith’s running around. grin. I visually verified that there are no “duplicate” names in my current table. Since the tblParticipant is pretty much done (We don’t get that many new additions lately), I think it would be safe enough to do so.

I’ll try that idea of yours. I had the same idea, tried it out and it still accepted the entry. Although, I had done it via table instead of a form.

Reply | Quote

Odd that it accepted duped values after you established the combination key. If you try it and it doesn’t work out, post back and we can see what’s up. BTW, I’ve never known an honest-to-goodness John Smith!

Reply | Quote

I haven’t met a John Smith myself either.

I’ll try it again…What I didn’t mention was that I actually set it to “triple key” (ParticipantID, LastName and FirstName) Would this have made it happen that way?

Reply | Quote

Forgive me if I’m missing something but, wouldn’t ParticipantID always be unique? Therefore, you’d never see a duplicate.

Reply | Quote

You didn’t miss anything here.

This table has unique ID using AutoNumber and Index set to Indexed (No Duplicate) but if an user adds a new person, the table automatically creates a new ID number which unfortunately, is different from the old record which has the exact same person.

Reply | Quote

Oh. Yes, your 3 field combo key is a bit ineffective for what you’re trying to do, e.g. John Smith w/ ID#1 is NOT the same as John Smith w/ ID#2 if you’re using a ID-FirstName-LastName multi-field key.

Perhaps the best route is a data checking routine along the lines of what Bart proposed in reponse to your initial post?

Reply | Quote

Thanks to everyone for their help. I appreciate it!

Reply | Quote

This may not be addressing the problem at hand, but is there any way you can have your data entry people use a different way to identify a person? Perhaps a unique EmployeeID number or Social Security Number (if in the US), or some other unique number used within your organization?

This would certainly solve the duplicate name issue.

HTH

Reply | Quote

Hi Brent,
Just for information, how do you determine whether participants of the same name are in fact the same person (address for example?) It’s probably worth designing your entry form so that it pops up a message alerting the user when a name already exists and querying if they really want to add a new entry. You could have the form display the original entry so that they can check if it is the same one and if not, they can add a new one.
Just some thoughts for what they’re worth.

Reply | Quote

My apologies for not replying any sooner. I was out for 4 days for the Memorial’s weekend…

SteveH: Your tip helped immensely! That was exactly what I needed. I never knew about manipulating indexes. Simply amazing.

For rest of you: I really appreciate your feedback and input. Most of you warned me about duplicate names, different person scenario. I’ve decided to add birthdate to the multiple index that SteveH suggested. The odds of person having exact same name and birthdate is astronomical. Since the mailing list only has 1,000 names and I don’t believe it will even hit 1,500 next 5 years, especially when we prune it every year, I don’t believe we will run into duplicate name, birthdate and different persons. grin.

Again, I appreciate your time and effort in helping me.

Reply | Quote

You can set an Index to No Duplicates over several fields in a table without it being part of the Primary Key.
Search Access Help for “Prevent duplicate values from being entered in a combination of fields”.
HTH

Reply | Quote