Prevent Duplicate people from being entered into Employee table

[ezra]

I have created an employee database in MS Access 2003. However, I have now discovered a couple of cases of the same person being entered into the database two or more times (The problem being they have worked for two or more departments over the years).

How can I set up some kind of validation when record is entered that if a person's name and Date of Birth match someone already in the database, a message box or something will pop up and say "This person already seems to be in the database"?

Thanks

[MikeTheBike]

I have created an employee database in MS Access 2003. However, I have now discovered a couple of cases of the same person being entered into the database two or more times (The problem being they have worked for two or more departments over the years).

How can I set up some kind of validation when record is entered that if a person's name and Date of Birth match someone already in the database, a message box or something will pop up and say "This person already seems to be in the database"?

Thanks

Hi

Perhaps you could adapt this function/code to inclued DOB.

Private Function NameOK() As Boolean
    Dim NameCount As Integer
    NameOK = True
    
    If Surname & "" = "" Or Forename & "" = "" Then Exit Function
    If Surname = Surname.OldValue And Forename = Forename.OldValue Then Exit Function
        
    NameCount = DCount("Surname", "tblEmployees", "Surname = """ & Surname & """ AND Forename = """ & Forename & """")

    If NameCount >= 1 And Me.Dirty Then
         MsgBox "An employee with this name already exists." & vbLf & vbLf & _
                "One name must be altered.", vbCritical, "Employee Name"
        NameOK = False
        If Not Me.NewRecord Then Me.Undo
    End If
    
End Function

You then decide which event to use it in, for instance in the control(s) Exit Event ie

Private Sub Forename_Exit(Cancel As Integer)
    If Not NameOK Then
        Cancel = True
    End If
End Sub

??

MTB

[mshmyob]

Or you could design your database properly and you wouldn't have this problem of data redundancy occuring. You obviously need a minimum of 3 tables - 1 for the employee, 1 for the department, and a bridge table between the 2 with date fields such as Start_Date, End_Date. You need then to identify your business rules such as Each employee may belong to many departments but not during the same date period. Each department can have many employees. This way an employee is only ever entered once but may move between departments and you have a history of their employment.

This will stop the problem you are having.

Checking for a person's DOB and comparing it to a name is not the way to properly do this. Think of an organization like MS - many thousands of employees across the world - i am sure they have people with the same name and DOB - not a good solution.

You design properly to start with you avoid all these problems.

cheers,
Rob

[ezra]

Hi

Perhaps you could adapt this function/code to inclued DOB.

Private Function NameOK() As Boolean
    Dim NameCount As Integer
    NameOK = True
    
    If Surname & "" = "" Or Forename & "" = "" Then Exit Function
    If Surname = Surname.OldValue And Forename = Forename.OldValue Then Exit Function
        
    NameCount = DCount("Surname", "tblEmployees", "Surname = """ & Surname & """ AND Forename = """ & Forename & """")

    If NameCount >= 1 And Me.Dirty Then
         MsgBox "An employee with this name already exists." & vbLf & vbLf & _
                "One name must be altered.", vbCritical, "Employee Name"
        NameOK = False
        If Not Me.NewRecord Then Me.Undo
    End If
    
End Function

You then decide which event to use it in, for instance in the control(s) Exit Event ie

Private Sub Forename_Exit(Cancel As Integer)
    If Not NameOK Then
        Cancel = True
    End If
End Sub

??

MTB

Thanks. I'll try this out.

[youmike]

Rob's advice is the best way forward, I'd say. Your employee table should include a unique identifier for each employee and the entry routine for new employees should include a test for date of birth and last name. The only time this wouldn't work is if your organisation employs twins!

Another problem might be mis-spelling of last names - it happens all too often. Here I'd suggest that the add routine returns a list of all people with the same date of birth already entered, showing their full names and departments and having the user confirm that this really is a new entry.

Finally, I'd log the the user details and time when each entry is made and let it be known that this logging takes place. It is a fact that users take a little more care when they know that their actions are subject to scrutiny.

When adding this sort of refinement, though, you need to balance the consequences of duplicate entries against the time taken to build in and maintain such checks.

[ezra]

Rob's advice is the best way forward, I'd say. Your employee table should include a unique identifier for each employee and the entry routine for new employees should include a test for date of birth and last name. The only time this wouldn't work is if your organisation employs twins!

Another problem might be mis-spelling of last names - it happens all too often. Here I'd suggest that the add routine returns a list of all people with the same date of birth already entered, showing their full names and departments and having the user confirm that this really is a new entry.

Finally, I'd log the the user details and time when each entry is made and let it be known that this logging takes place. It is a fact that users take a little more care when they know that their actions are subject to scrutiny.

When adding this sort of refinement, though, you need to balance the consequences of duplicate entries against the time taken to build in and maintain such checks.

Thanks for the user logging suggestion. I will definitely use this.