Avoid duplicate names being added

[RobPolley]

I have a web form for new customers to add their name. It fills a database called "new." I go to each newly submitted record via a desktop form. For every record, I click a button that searches main database (not the "new" database) for anyone with the same last name. Then I look through the list it provides for anyone with the same first name. If I find a match, I check their date of birth, address, phone, email, etc. I then decide if the person is already in the main dbf, or not, and either add them, or not.

I'd like to further/fully automate this process. Is there a standard for determining duplicate names? I have first name, last name, date of birth, address, city, state, zip, telephone, and email. My hunch is that I can write something that will fully automate the search, but I thought I'd ask what the standard may be. I'm thinking it's something like "alltrim(first)+alltrim(last)+date of birth. But Billy Jones and Bill Jones would show up as different people with that formula, so I know that's not quite it.

If anyone has "the standard," I'd appreciate it. On the other hand, this may be much more difficult than I'm thinking.

Thank you.

--Rob

[Keith Hubert]

Re: Avoid duplicate names being added

Hi Rob,

You have posed an interesting question.

My question to you is when do you want this duplicate check to take place?

Before or after the data is submitted?

If it is before then you should repost your question in the Server section with regard to just that point.

If it after, then you will need to consider which fields you want to work with, that you know will make a unique record, but would allow for more than one person from the same address to register, if that is what you wanted.

[Tom Henkel]

Re: Avoid duplicate names being added

You are getting into the realm of probability here. It is very difficult to automate a process when one person can enter their name as Bill Jones, Billy Jones, William Jones, Wil Jones, etc. Many times, birthdates can be entered incorrectly, addresses as well. Most times, it takes a human eye to make a clear determination.

My thought is that you can get a close estimate that they are the same, and then display the records that you think may be the same person, and take action once viewed.

We have an enormous Client Information system, and we try to avoid duplication based on name, address, birthdate, and ssn. The main issue is that a human is entering the information and as such is prone to typing possible incorrect information, so "duplicates" are unavoidable. I have given our data entry people 7 different ways to search for an existing client, and still duplicates arise. The name search that I have created will present a list of clients with the given name, along with ssn, address, and birthdate. The operator can then review the list against the information on the sheet of paper that they are keying from and either select the one that matches (or very closely matches) or begin entering a new record.

I hope this helps,

Tom

[Peter.Greulich]

Re: Avoid duplicate names being added

My thought is that you can get a close estimate that they are the same, and then display the records that you think may be the same person, and take action once viewed.
...
The name search that I have created will present a list of clients with the given name, along with ssn, address, and birthdate. The operator can then review the list against the information on the sheet of paper that they are keying from and either select the one that matches (or very closely matches) or begin entering a new record.

I do something very similar to what Tom does.

Image 1: The user presses "new client" and a dialog pops up for the name. If the name is unique (based upon a soundex check), pressing OK on the dialog opens the new client entry form. If the name is not unique, the "Client Name Check" form opens up (image 2).

Image 2: The user has the option of checking the names to see if the client has already been entered, or editing the client if the entity exists (if necessary), or entering the new client.

---
Note: the names & addresses in the form image have been changed to protect the guilty.

[RobPolley]

Re: Avoid duplicate names being added

You've all confirmed my hunch that there is no way to automate this perfectly, and that's very helpful. I will be able to take your ideas to further automate the process I'm using.

Many thanks!

--Rob

[RobPolley]

Re: Avoid duplicate names being added

Update:

I'm reading Dr. Peter Wayne's x basic for everyone book. He has a routine for avoiding duplicate names that's worth exploring. It's on page 73. I'd paste it here, but it's his work, and it alone is worth the $20 for the book.

--Rob