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

Creating a custom identifier

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


FishVal
Expert 2.5K+
P: 2,653
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
Sep 3 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
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)>
Sep 3 '08 #3

P: 14
Hello Fish,

Thank you for your prompt response.

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
Sep 3 '08 #4

P: 14
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.


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)>
Sep 3 '08 #5

P: 14
In my response below I meant setting that required information/ comprehensive applications are NOT enforced.


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.
Sep 3 '08 #6

Post your reply

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