How to find differences in one field in a table

[Zonie32]

Hi. I have a table with one of the fields as AGENTNAME. There can be many duplicates. This data is entered by several employees and not all of them enter the names the same way. Some forget the middle initial, some put a period after the middle initial, some put too many spaces by mistake when typing the name, ie...

SMITH, JOHN J.
SMITH, JOHN J
JONES, BOB R
JONES, BOB R.

My question is how can I find out which names have differences? Do I need a query? If so, how do I write it?

Any help is appreciated.

 

[dhookom]

SQL allows you to select records with the SAME values. What would you expect to see from the samples you provided? The four values are all different.

You should be storing first, last, and middle names in separate fields.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[BobStubbs]

You can reduce the likelihood of name input errors by doing the following:

-- Force users to input data via a form - never directly into the table.
-- Collect Surname and Forename in separate fields, as dhookom advised in his reply.
-- Remove any non-alphabetic characters from these fields (but remember to allow ' for names such as O'Neill)
-- If you need a list of names in a single field, e.g. for lookup or sorting purposes, create a ListName field which is generated by Access, and which users cannot modify directly.

Here is some of my VBA code, which shows how I achieve these results. In my example, my form and the underlying table contain three text fields, named:

EmployeeSurname
EmployeeForename
EmployeeListName

EmployeeListName on my form has these properties set, to prevent data entry directly into the field:

Enabled=Yes
Locked=Yes

In the BeforeUpdate event of the form, I have:

EmployeeListName = EmployeeSurname & ", " & EmployeeForename

This creates a neat list format for names, e.g.

Jones, Peter
Jones, William
Smith, John
Wilson, Ann
etc

Here is my CleanString function.

Function CleanString(strOneLine As String) As String

Dim I As Integer

Dim strOutLine As String
Dim strOneChar As String
Dim strAllowed As String

'---------------------------------------------------
'- Set up a string of allowed characters.  In this -
'- case, A to Z and a to z plus single quote '     -
'---------------------------------------------------
strAllowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'"

'---------------------------------------------------
'- If an empty string is passed to the function,   -
'- just exit.                                      -
'---------------------------------------------------
If strOneLine = "" Then
    strOutLine = ""
    Exit Function
End If

'---------------------------------------------------
'- Build an output string containing the valid     -
'- characters from the input string                -
'---------------------------------------------------

For I = 1 To Len(strOneLine)
    strOneChar = Mid$(strOneLine, I, 1)
    If InStr(strAllowed, strOneChar) > 0 Then
        strOutLine = strOutLine & strOneChar
    End If
Next I

CleanString = strOutLine

End Function

This is placed in a module, and called from the AfterUpdate event of the surname and forename fields, like this:

EmployeeSurname = CleanString(EmployeeSurname)

I hope that these ideas will be useful.



Bob Stubbs