Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preventing duplicate names

    I have a problem that is driving me nuts...

    In my Participant table, I keep First Name and Last Name in separate fields. However, the users keep on entering duplicate names and setting each field to prevent duplicates doesn't work because there are many same last names such as "Anderson". I need a way to force the table to look at both fields and determine whether it is a duplicate or not.

    I've already told users many times to double check, but they still end up adding a new record for same person that already exists in the database.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    Write a function to check if the lastname and the firstname already exists in your database. In the function you can use the dCount function like this:

    dim intNumber as long

    intNumber = dCount("LastName", "YourTableName", "Lastname=' & trim(me![txtLastname]) & "' AND Firstname='" & trim(me![txtFirstname]) & "'")
    if intNumber > 0
    It exists already
    Do something usefull, give a message!
    endif

    If you call this function on the right event of your form (on update for instance) you can take some appropriate actions.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    Shane,

    You are right, there are a lot of John Smith's running around. grin. I visually verified that there are no "duplicate" names in my current table. Since the tblParticipant is pretty much done (We don't get that many new additions lately), I think it would be safe enough to do so.

    I'll try that idea of yours. I had the same idea, tried it out and it still accepted the entry. Although, I had done it via table instead of a form.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    Odd that it accepted duped values after you established the combination key. If you try it and it doesn't work out, post back and we can see what's up. BTW, I've never known an honest-to-goodness John Smith! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    I haven't met a John Smith myself either. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    I'll try it again...What I didn't mention was that I actually set it to "triple key" (ParticipantID, LastName and FirstName) Would this have made it happen that way?

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    Forgive me if I'm missing something but, wouldn't ParticipantID always be unique? Therefore, you'd never see a duplicate. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    You didn't miss anything here. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    This table has unique ID using AutoNumber and Index set to Indexed (No Duplicate) but if an user adds a new person, the table automatically creates a new ID number which unfortunately, is different from the old record which has the exact same person.

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    Oh. Yes, your 3 field combo key is a bit ineffective for what you're trying to do, e.g. John Smith w/ ID#1 is NOT the same as John Smith w/ ID#2 if you're using a ID-FirstName-LastName multi-field key.

    Perhaps the best route is a data checking routine along the lines of what Bart proposed in reponse to your initial post?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    Thanks to everyone for their help. I appreciate it! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    You can set a mutli-field key, which should accomplish your design goal. In design view for the table, select both the first and last name fields, then hit the Key button. Walla!

    I do have to wonder, though, if you really want this. Might you have two people with the same first and last name but are two distinct people? Lots of John Smith's running around in the anglo-world.

    Edited by Charlotte to remove excess space
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  11. #11
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    You can set an Index to No Duplicates over several fields in a table without it being part of the Primary Key.
    Search Access Help for "Prevent duplicate values from being entered in a combination of fields".
    HTH
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    This may not be addressing the problem at hand, but is there any way you can have your data entry people use a different way to identify a person? Perhaps a unique EmployeeID number or Social Security Number (if in the US), or some other unique number used within your organization?

    This would certainly solve the duplicate name issue.

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Preventing duplicate names

    Hi Brent,
    Just for information, how do you determine whether participants of the same name are in fact the same person (address for example?) It's probably worth designing your entry form so that it pops up a message alerting the user when a name already exists and querying if they really want to add a new entry. You could have the form display the original entry so that they can check if it is the same one and if not, they can add a new one.
    Just some thoughts for what they're worth.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing duplicate names

    My apologies for not replying any sooner. I was out for 4 days for the Memorial's weekend...

    SteveH: Your tip helped immensely! That was exactly what I needed. I never knew about manipulating indexes. Simply amazing.

    For rest of you: I really appreciate your feedback and input. Most of you warned me about duplicate names, different person scenario. I've decided to add birthdate to the multiple index that SteveH suggested. The odds of person having exact same name and birthdate is astronomical. Since the mailing list only has 1,000 names and I don't believe it will even hit 1,500 next 5 years, especially when we prune it every year, I don't believe we will run into duplicate name, birthdate and different persons. grin.

    Again, I appreciate your time and effort in helping me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •