Issue with duplicate first and last name combinations

[bfotk]

In our organization's database we currently have five pairs of members whose first and last names are the same as another member's: John Doe and John Doe, for example. This has led to occasional confusions, one instance significant enough to lead to a threat of legal action.



We can (so far) use middle initials to help, but the office clerks do not always take note. Days sometimes get busy, tasks get rushed...

As a stopgap, I highlighted the middle initial field on the main data form for all members to attract attention to it: bright yellow background rather than white and a bolded letter besides. It turned out (I should have thought of this) that highlighting EVERYBODY's middle initial became equivalent to highlighting NOBODY's middle initial.

I'd like to have the highlighting happen only when a member's first and last name combination matches that of another member. Further, I'd like to have this be ongoing. When a new member is added, the first + last should be checked against current members and the necessary step taken. That should also happen if a member happens to change his or her first or last name and creates a duplication.

The find duplicates query wizard seems to be designed to find duplicates with the idea of eliminating them. I need to find duplicates with the goal of working with them.

Suggestions please!

[Micron]

Answer depends on what this db is being used for. If the concern is over log-ins, then that's easy enough. Use unique combinations of name parts.
If it is for choosing the right John Doe when entering that he showed up for his appointment, get some sort of unique identifier such as SIN or SSN or phone numbers.
If it's neither, ask all John Doe's to consider going by a nick name.

If none of that applies, we need more info about the nature of the business.

Funny enough, my wife just asked a friend the other day "why do you sometimes go by Rex?" It was because there were too many with his name, so they adopted nicknames and it kind of stuck with him.

EDIT

I'd like to have the highlighting happen only when a member's first and last name combination matches that of another member.

IMHO, hilighting is a non starter. If users can't be precise enough in their daily routine, then you need a hammer - like a message prompt.

[CJ_London]

you need to use conditional formatting for the initials control - rule would be something like

expression is .....dcount("*","tblEmployees","firstname='" & [firstname] & "' and lastname='" & [lastname] & "")>1

[orange]

I agree with the other responses. Also it would be helpful to know the context of the problem and the scope.
I suppose EmployeeNumber or similar is out of the question?

[bfotk]

I agree with the other responses. Also it would be helpful to know the context of the problem and the scope.
I suppose EmployeeNumber or similar is out of the question?

Not at all. The issue is how to grab those numbers for the duplicated names. The find duplicates query wizard misses, as far as I've been able to determine.

[bfotk]

First off, thanks to all. Now for some specifics.

We are a union. We deal with certain health related benefit issues. Sending information to the wrong member can literally be a federal offense.

There are ID numbers from the employer that are included in our member data as well as the internal personal key ID numbers that are generated by Access. Yes. I'd like to arrange things so that any member whose first+last combination is the same as that of another member would have a NOTE: Duplicated Name message box displayed on his or her main data screen.

We already do something similar for members who are participants in an union option. Their IDs are in a table for that option and someone maintains that table, adding and removing members according to the members' wishes. The general data screen for any member will have a box at the top showing "Option Member." That's easy. If the person's ID is found in the Option table, the box appears.

As I said above, the find duplicates wizard seems not to apply. Perhaps it could be tuned to do what I want. Perhaps some code is called for. Maybe SQL. I have a certain amount of experience with both but I'm not an expert with either.

[CJ_London]

As I said above, the find duplicates wizard seems not to apply. Perhaps it could be tuned to do what I want. Perhaps some code is called for. Maybe SQL.

have you tried the solution in post #3?

[Micron]

I don't understand why names come in to play here at all. EVERYTHING should be based on employee number as a unique ID. If they're involved at all, it's "what's your number?" not what's your name.

[orange]

The reason for unique identifiers is to avoid duplication. You can search/retrieve based on ID numbers(unique) and display the corresponding name. But you can search by a non unique field value and expect to get unique results.
Try Ajax's approach.

[bfotk]

I don't need the count of how many duplicate firstlast exist. I need the unique ID of every person who is part of such a pair. I can get the count. In fact, here it is, courtesy of the Access Find Duplicates query wizard (I've changed the names):

FirstLast NumberOfDups
Andy Andrews 2
Brian Brothers 2
Charles Cousins 2
Donald Danielson 2
Egbert Edwards 2

What I want is this:

FirstLast EmpID
Andy Andrews 1234
Andy Andrews 2678
Brian Brothers 3012
Brian Brothers 4456
Christy Cousins 6890
Christy Cousins 5321
Donna Danielson 7655
Donna Danielson 6677
Egbert Edwards 5556
Egbert Edwards 2121

[CJ_London]

your requirement was stated as

i'd like to have the highlighting happen only when a member's first and last name combination matches that of another member.

All the count is doing is determining if there is more than one match and if so, highlight the initial

You now seem to want something different - or perhaps you have fixed in your mind there is only one way to solve your problem.

If you do want something different, please clarify what it is you actually want. Otherwise try my suggestion before suggesting it is not what you want

[ArviLaanemets]

You probably have employees table like tblEmployees: EmployeeID, FirstName, LastName, ...;
In all other tables, where you need to attach an employee to, you must have a EmplyeeID field as foreign key;
In any form where you need to select or display employee name, you'll use a combobox for this. The recordsource for combobox will be a query like

SELECT EmployeeID, FirstName & " " & LastName & "(" & CStr(EmployeeID) & ")" AS FullName FROM tblEmployee ORDER BY 2

The Source property for combo will be EmployeeID, the FieldCount property will be 2 with BoundColumn property set to 1, and ColumnWidths property set like "0;2.5" (essential is to set bound column with to 0)

The combo will display e.g. "Andry Andrews (1234)", but really it's value will be like 1234.

[bfotk]

At the risk of sounding hopeless, I need some guidance in trying your suggestion. I devoted serious time to it yesterday and couldn't get myself to a point of being able to say "Yes, that's where I need to go" or "No, I need something different." I do lack some skills and understanding even though I've been doing this stuff for going on twenty years. (Maybe I'm just wearing out.)

Let me give a scenario that might help me clarify.

A clerk need to send a letter in the mail to Martha Martin. The clerk doesn't know Martha's employee ID...in fact, Martha probably doesn't know her own ID. The clerk uses "Martin" and works through to "Martha" but isn't aware that there's another Martha Martin among the 3500 members. The clerk sees the address, the work site, etc, but probably doesn't know those particulars. The clerk sees the button to print an envelope and hits it. The letter goes out to the wrong Martha Martin.

That's the problem that needs to be solved. It could be implemented by that highlighting idea or by turning the Envelope button red when there's another Martha Martin or having the Envelope button pop up a message box warning of another Martha Martin and getting confirmation before printing or by having a "Duplicated Name" box showing somewhere on Martha's info screen.

The nasty bit about how I should implement a trial of your suggestion remains. I'm abashed but not totally ashamed to say so.

[ArviLaanemets]

When using combo box to select full name, and having combo's record source ordered by full name, when clerk starts typing, and types e.g. "Martha" into combobox, the record source list displays all employees starting with "Martha...". When clerk contuinues typing and gets "Martha Martin" entered, all employees whose full names start with "Martha Martin..." are displayed, etc. When a single employee remains to select, there is only one Martha Martin. When there are several to select, the clerk must somehow decide, which one to select.

ID was as example. You can have any info available in your database instead - department, appointment, age, whatever.

[orange]

bfotk,

What other info do you have for Employees that may be useful/helpful in distinguishing one from another?

You may be able to create something based on the following. We often had possible duplication/replication of Company info.
To discriminate we used an algorithm based on these fields

Legal Name
Operating Name
Alias Name
PostalZip Code
Phone number
email
City
State

Depends on your needs. But you will need some field or combination of fields to isolate the individual.

Good luck with your project.