473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Creating a custom identifier

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
5 1533
FishVal
2,653 Expert 2GB
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
3,532 Expert 2GB
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
Coni
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
Coni
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
Coni
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

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

Similar topics

4
by: Cengiz Ulku | last post by:
Hi all, As a general question: Is it possible to create custom file formats? I do not mean changing the attribute of a known file. For ex: A text file transformed into a .xyz will still be...
7
by: Robin | last post by:
In a current .Net solution (using VB.Net) has a 3 tier architecture of Web interface, Data Access Layer and Database. How do I implement business logic and class layers into this solution?
31
by: JoeC | last post by:
I have read books and have ideas on how to create objects. I often create my own projects and programs. They end up getting pretty complex and long. I often use objects in my programs they are...
5
by: Marcin Gil | last post by:
Hi! I have the code like this (obvious things like ctor/dtor removed) typedef struct _NODE { int val; int index; } Node;
8
by: DaTurk | last post by:
Hi, I was just curious how you would go about creating a unique identifier with 3 ints.
3
by: AMD_GAMER | last post by:
I currently have a website which is using ASP.NET 2.0, C#, and SQL Server 2005. The website will be used to enter grants for a university. When a new grant is entered, I need to generate a primary...
0
by: Jeff | last post by:
I have been using a view state decoder utility to understand the contents of ViewState. In looking at the decoded contents of many aspx pages (from my own projects and from asp.net sites out on...
13
by: mliptak | last post by:
I'm trying to implement logging in my application, so that each log message has its unique identifier, e.g. log(identifier, text) What I want to achieve is that the compiler screams if the log()...
1
by: =?Utf-8?B?Q2hhcmxlcw==?= | last post by:
I'm running VS2008 & attempting to solve a problem I've encountered while developing some software for our business. Here's the basic idea...I've created a class that represents a file (with...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.