Creating a custom identifier | Newbie | | Join Date: Sep 2008 Location: New York
Posts: 14
| | |
I am working with a database that was created by someone else. However, there seems to be a lot of duplicates in the database because the same person may be entered twice. The name of the table is Client and the name of the fields are Client ID, FirsNamet, LastName, MiddleName, Date of Birth, SS# etc. However, because social security is not required, it is not used as a primary key. Client ID is the primary key (autonumber). Client ID #1 and #32 can have the same client information, but it is not easy to detect duplicate unless a query is run to discover duplicates. There has been an incident where two clients have the same First, Last and middle names so combining these fields are not reliable. Sometimes the DOB is omitted because at the time, the information was not available. I need help on resolving this issue. How do I create a unique identifier that will help to eliminate dupblicates.
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Creating a custom identifier
Hello, Coni.
As far as I've understood your situation you don't need an identifier to use it as PK (you already has one) one the one hand, and, on the other hand, it will be somewhat difficult (if possible at all) to implement table-level index because some fields could be omitted when record is created and combinations of others are not supposed to be unique.
I would suggest you to use form-level data validation which will warn user about possible duplication in a case non-empty fields values of a record to be created are the same as for already existing one.
Regards,
Fish
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 2,997
| | | re: Creating a custom identifier
As Fish has already said, you already have a unique identifier, your ClientID field, but what you need to do is to implement form-level checking to prevent the same person from being entered twice, thus receiving two "unique" identifiers!.
Looking at your data, you have a couple of choices. If your situation is such that no record should have the same SS# (there are situations where this is possible) you can check the SS# for duplicates. This means that you have to
1) Require that the field be populated before the record is saved.
2) Check the entered SS# for duplicates, using DLookUp()
Validation for # 1 above would be done in the Form_BeforeUpdate event.
The check for dupes in # 2 above would be done in the SS# field's BeforeUpdate event.
The other obvious way, more complicated, would be to check for the same First, Middle and Last name as well as the DOB.
Once again, you'd have to require that the DOB field be populated, as described above for # 1.
As far as checking what you already have in your records, I'm afraid that without always having either a SS# or a DOB, you're really stuck with going thru your records. You can run a query to pull seemingly duplicate names, but you're then going to have to do some manual checking to see whether or not they're are, in fact, duplicates, and enter one of the unique pieces of data that differentiates them.
Welcome to Bytes!
Linq ;0)> | | Newbie | | Join Date: Sep 2008 Location: New York
Posts: 14
| | | re: Creating a custom identifier
Hello Fish,
Thank you for your prompt response. Quote:
Originally Posted by FishVal Hello, Coni.
As far as I've understood your situation you don't need an identifier to use it as PK (you already has one) one the one hand, and, on the other hand, it will be somewhat difficult (if possible at all) to implement table-level index because some fields could be omitted when record is created and combinations of others are not supposed to be unique.
I would suggest you to use form-level data validation which will warn user about possible duplication in a case non-empty fields values of a record to be created are the same as for already existing one.
Regards,
Fish | | Newbie | | Join Date: Sep 2008 Location: New York
Posts: 14
| | | re: Creating a custom identifier
Thank you Linq for helping out. I will try this. Obviously in the setting that I work that does enforce required information, problems like this occurs where there are blank fields because information was not available. I Tried enforcing the before update event to not save record if a particular information is missing but I have no control over this matter because the information has to be entered in the system regardless. So the prompt was remove. I forsee a disasterous database which it already is but, I working on creating guidelines for the other departments that receive information to get more comprehensive application to be entered in the database. Thanks for your input. Quote:
Originally Posted by missinglinq As Fish has already said, you already have a unique identifier, your ClientID field, but what you need to do is to implement form-level checking to prevent the same person from being entered twice, thus receiving two "unique" identifiers!.
Looking at your data, you have a couple of choices. If your situation is such that no record should have the same SS# (there are situations where this is possible) you can check the SS# for duplicates. This means that you have to
1) Require that the field be populated before the record is saved.
2) Check the entered SS# for duplicates, using DLookUp()
Validation for # 1 above would be done in the Form_BeforeUpdate event.
The check for dupes in # 2 above would be done in the SS# field's BeforeUpdate event.
The other obvious way, more complicated, would be to check for the same First, Middle and Last name as well as the DOB.
Once again, you'd have to require that the DOB field be populated, as described above for # 1.
As far as checking what you already have in your records, I'm afraid that without always having either a SS# or a DOB, you're really stuck with going thru your records. You can run a query to pull seemingly duplicate names, but you're then going to have to do some manual checking to see whether or not they're are, in fact, duplicates, and enter one of the unique pieces of data that differentiates them.
Welcome to Bytes!
Linq ;0)> | | Newbie | | Join Date: Sep 2008 Location: New York
Posts: 14
| | | re: Creating a custom identifier
In my response below I meant setting that required information/ comprehensive applications are NOT enforced. Quote:
Originally Posted by Coni Thank you Linq for helping out. I will try this. Obviously in the setting that I work that does enforce required information, problems like this occurs where there are blank fields because information was not available. I Tried enforcing the before update event to not save record if a particular information is missing but I have no control over this matter because the information has to be entered in the system regardless. So the prompt was remove. I forsee a disasterous database which it already is but, I working on creating guidelines for the other departments that receive information to get more comprehensive application to be entered in the database. Thanks for your input. |  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,366 network members.
|
