member database problem..

[enriquemellado]

ok
here it is
i have a database for members.
i have a table with the general info for the members like name, date of birth, that kind of stuff.
i want to know how i can make sure that there are no duplicate members.
when in the form.. how can i validate that the member isn't already there?

the problem is i can't validate the name for duplicates because there isn't only one John in the world. or the name might match all together but the birthdate isn't the same.. things like that
i'm sure this is a very common problem so there might be an established solution
i made a search for duplicate reord. but not sure what to do with it

i was thinking of creating a special value using firstname-lastname-birthdate so they'd be unique... but even after doing that. how can i make sure the record isn't added to the table until it is validated?
hope i was clear with my question.. thanks for the help

 

[Moniker]

i'm sure this is a very common problem so there might be an established solution

There is! The search function in here works.

 

[enriquemellado]

i tried that...
most of the posts i found were not answered completely.
the colsest i found was creating an index...
is this the most effective way?
thanks for the help

 

[neileg]

If you step away from Access and look at the real world situation again, you need to recognise that there is no guarantee that any combination is unique. It may be very unlikely that there will be two people with the same name and the same birthdate, but not impossible. Add to that the variances in what people call themselves, Bill, William, Will, Billy, etc, changes of surname on marriage and the odd spelling mistake and you soon see that there can be duplicate people that have different exact names.

So, while you can apply a multi field index set to no duplicates, this has the chance of preventing a (rare) genuine duplicate in all three fields and will not guarantee that the same person is not added again because of variances in the name.

What you might do is test the data using the most reliable piece of information (maybe the birthdate) and look for near matches in the other data (maybe the first three characters of the surname) and get the operator to check for duplicates. To do this you would need to capture the data outside of your main table in either an unbound form or a form bound to a temporary table, validate the data and append this to your table once you are satisfied it is valid.

But bear in mind that Access can't 100% guarantee to catch all the problems. Human error beats the machine every time!