By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,824 Members | 2,359 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,824 IT Pros & Developers. It's quick & easy.

Prevent Duplicate people from being entered into Employee table

P: 11
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
Sep 22 '08 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 634
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.
Expand|Select|Wrap|Line Numbers
  1. Private Function NameOK() As Boolean
  2.     Dim NameCount As Integer
  3.     NameOK = True
  4.  
  5.     If Surname & "" = "" Or Forename & "" = "" Then Exit Function
  6.     If Surname = Surname.OldValue And Forename = Forename.OldValue Then Exit Function
  7.  
  8.     NameCount = DCount("Surname", "tblEmployees", "Surname = """ & Surname & """ AND Forename = """ & Forename & """")
  9.  
  10.     If NameCount >= 1 And Me.Dirty Then
  11.          MsgBox "An employee with this name already exists." & vbLf & vbLf & _
  12.                 "One name must be altered.", vbCritical, "Employee Name"
  13.         NameOK = False
  14.         If Not Me.NewRecord Then Me.Undo
  15.     End If
  16.  
  17. End Function
You then decide which event to use it in, for instance in the control(s) Exit Event ie
Expand|Select|Wrap|Line Numbers
  1. Private Sub Forename_Exit(Cancel As Integer)
  2.     If Not NameOK Then
  3.         Cancel = True
  4.     End If
  5. End Sub
??

MTB
Sep 22 '08 #2

mshmyob
Expert 100+
P: 903
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
Sep 22 '08 #3

P: 11
Hi

Perhaps you could adapt this function/code to inclued DOB.
Expand|Select|Wrap|Line Numbers
  1. Private Function NameOK() As Boolean
  2.     Dim NameCount As Integer
  3.     NameOK = True
  4.  
  5.     If Surname & "" = "" Or Forename & "" = "" Then Exit Function
  6.     If Surname = Surname.OldValue And Forename = Forename.OldValue Then Exit Function
  7.  
  8.     NameCount = DCount("Surname", "tblEmployees", "Surname = """ & Surname & """ AND Forename = """ & Forename & """")
  9.  
  10.     If NameCount >= 1 And Me.Dirty Then
  11.          MsgBox "An employee with this name already exists." & vbLf & vbLf & _
  12.                 "One name must be altered.", vbCritical, "Employee Name"
  13.         NameOK = False
  14.         If Not Me.NewRecord Then Me.Undo
  15.     End If
  16.  
  17. End Function
You then decide which event to use it in, for instance in the control(s) Exit Event ie
Expand|Select|Wrap|Line Numbers
  1. Private Sub Forename_Exit(Cancel As Integer)
  2.     If Not NameOK Then
  3.         Cancel = True
  4.     End If
  5. End Sub
??

MTB
Thanks. I'll try this out.
Sep 22 '08 #4

P: 69
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.
Sep 23 '08 #5

P: 11
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.
Sep 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.