Standard name validation - best practice

[Robert Robinson]

I have a contact list table that includes client name in 3 fields (first,
middle, last name). I would like to set up validation using the entire name.
What is the most efficient VBA coding of this event:
1) Is it best to concatenate F/M/L name in one field?
2) If #1 is yes, should this concatenated field be added to the contact list
table, then have new entries validate against this field? If not, where
should the concatenated field be set up?
3) Should the validation be included in the "Before Update" event property?
4) Are there any other tips to a proper setup of this validation event?
Please advise.

 

[Allen Browne]

Robert, you generally cannot do this at the engine level.

Firstly, it is not unusual to have 2 people with the same name. I personally
know several people who have the same name and live at the same address
(father and son.) Sure, they have different birth dates, but is completely
impractical to require a birthdate in most databases, so you generally have
to design to allow duplicates.

This means you cannot use field- or table-level validation rules. Instead
you will use the BeforeUpdate event of the *form* to warn the user of
possible duplicates, but allow them to override the warning.

Concatenating the names is the least efficient approach, because Access
cannot use any indexes on the individual fields to get matches. Better to
build a WHERE clause that looks at the individual fields.

From there, there are several approaches you could take. You might want to
consider using a Soundex algorithm on the last name to get similar sounding
names, matching the initials (or prefix characters) of the first and middle
name, handling nulls in the non-required fields (such as Middle Name), and
limiting matches based on state or zip code.

In some apps where this is important (essentially CRM type databases), we
sometimes even make this user-configurable, so they are able to tune when
the warning are given (where they want more or fewer warnings.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Robert Robinson]

Allen -

Thank you for responding so quickly!

That point is something that I should have clarified...I do not want a fatal
error, rather, a warning so that the entry person does not go blythely along
duplicating transactions...this will be a database of female names, so a
validation against name and address will likely be more successful than it
would if men's names were included...

Where can I get more information on the Soundex algorithm? I am stepping
down from ERP applications to the world of Access and have heard of the
algorithm, but not its details.

 

[Douglas J Steele]

The original specs are described at
http://www.archives.gov/genealogy/census/soundex.html

Here's VBA code to implement that:

Function GetSoundex(ValueIn As String) As String

Dim lngIndex As Long
Dim strCurrChar As String
Dim strCurrVal As String
Dim strInput As String
Dim strPrevVal As String
Dim strSoundex As String

strInput = UCase$(ValueIn)

strSoundex = Left$(strInput, 1)
lngIndex = 1

Do While Not Len(strSoundex) = 4
If lngIndex > Len(strInput) Then
strSoundex = strSoundex & "0"
Else
strCurrChar = Mid$(strInput, lngIndex, 1)
Select Case strCurrChar
Case "B", "F", "P", "V"
strCurrValue = "1"
Case "C", "G", "J", "K", "Q", _
"S", "X", "Z"
strCurrValue = "2"
Case "D", "T"
strCurrValue = "3"
Case "L"
strCurrValue = "4"
Case "M", "N"
strCurrValue = "5"
Case "R"
strCurrValue = "6"
Case Else ' vowel, H, W, or Y
strCurrValue = "0"
End Select
End If
If (strCurrVal <> "0") Then
If (strCurrVal <> strPrevVal) Then
If lngIndex <> 1 Then
strSoundex = strSoundex & strCurrVal
End If
End If
End If
If strCurrChar <> "H" And _
strCurrChar <> "W" Then
strPrevVal = strCurrVal
End If
lngIndex = lngIndex + 1
Loop

GetSoundex = strSoundex

End Function


Do a Google search on Soundex, though: there are a few variations.

(I wrote about this, and another similar technique, Levenshtein Distance, in
my April, 2005 "Access Answers" column for Pinnacle Publication's "Smart
Access". Unfortunately, under the terms of my contract with Pinnacle, I'm
not allowed to publish my columns on my website until 6 months after the
issue. It'll be available for free download at
http://www.accessmvp.com/djsteele/SmartAccess.html sometime next month if
you can wait!)