Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 13th, 2004, 11:16 AM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default PK problem

Hi, All:
I have a table that store information about patient. In this table I only have firstname, lastname, ************, age. which one I should use to be the Primary key? How do I control the duplicate?
thanks
cindy


 
Old April 13th, 2004, 11:52 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

suggestion

Add PatientID as your PK with auto number.

You will have many patients with the same name, ************ age etc.

The patientID will uniquely make that distinction.

Hope it helps.



 
Old April 13th, 2004, 11:56 AM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks!
but if I have same person that have been entered twice, how do I know?
cindy

 
Old April 13th, 2004, 12:17 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can create a unique index on a combination of fields. The combination of FirstName plus LastName plus age is pretty close but a date of birth with names would be better. Adding Social Security Number to First and LastNames is also good.

When you create a unique index, no duplications are permitted in the combination of fields specified in the index.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
 
Old April 13th, 2004, 12:29 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Quote:
quote:Originally posted by reindeerw
 Thanks!
but if I have same person that have been entered twice, how do I know?
cindy

create a sorted query that will allow you view all records with does specific criteria and then determine which are duplicates. Once this is done delete duplicates.

 
Old April 14th, 2004, 08:44 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by reindeerw
but if I have same person that have been entered twice, how do I know?
As JeMacc suggested, create a lngPatientID as an autonumber in your table. This will uniquely identify the correct record every time in VBA code. To avoid duplicates, Access automatically sets the "Indexed" property of lngPatientID to YES (NO DUPLICATES) when you declare it as an autonumber. This will prevent duplication of the ID number (which no one would ever have to see).

But no one will ever see the ID number anyway, so you want a way to avoid putting a patient in twice based on what people type in, not only on the record seen by Access' eyes. Jürgen is correct in adding a social security number (SSN) to your table. However, if you do that, there is no need for a unique index on a combined field as he suggests because a SSN will never be shared by two people. On that field, also set Indexed to YES (NO DUPLICATES). In that case, Access will not allow you to enter the same person more than once.

The tricky part is when you treat foriegn patients or those without SSNs. In that case, you have to wonder what to do about a unique identifier. Again, Jürgen's suggestion is valid except that there is always a wild chance that (given the fields you mentioned) that a combined field can STILL result in a duplicate!

Still, the SSN is the best bet.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old April 14th, 2004, 10:33 AM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have frequently heard about American SSN numbers being reissued or duplicated in error. Although such duplications are rare, they may be intolerable. In addition, many countries have privacy legislation that limit casual collection of SSN numbers. There is a searchable archive of postings of a few years of discussion at the mailing list:

     www.databaseadvisors.com

The archive is actually hosted by Drew Wutka at:

     www.wolfwares.com

though the site is down for maintenance for a few days. The discussions about candidate keys and surrogate keys have resulted in threads of hundreds of posts in length. Duplication of SSN numbers has been reported in several postings by reliable members.

In Access, an autonumber key is probably the best and most efficient means to uniquely identify a record and it is by far the most efficient kind of field to join on to create a relationship to another table. The use of a compound index (non key) is primarily to prevent duplications of records at a database enforced level.

Even the existence of a compound index does not guarantee that duplications are not entered as data is often miskeyed, people use aliases and variations of spellings of their names (my name: Jürgen may be Jurgen, also spelled Juergen) and the spelling sometimes varies by gov't agencies that issue identification and people sometimes change their names, by marriage or otherwise. I have a grade school report card with four different spellings of my name by two teachers.

To detect duplications in these areas, it is important to store data in a consistent manner and with consistent data entry rules. I use a number of special queries that detect duplication on address (501-101 Elm Street = #501, 101 Elm ST = 5th Floor, 101 Elm Str.), on phone number, on names and other fairly standard fields. Of course there should be duplications on some of these fields since people do share phone numbers and addresses and names are not guaranteed unique (there were two named Allan Miller with the smae age in my 7th grade class of about 120 students). However, the use of a series of canned querys (based on the find duplicates query wizard) can help you identify out of date phone numbers, changed addresses and genuine duplicates as well as scam artists using aliases to deceive.

A multifield index is one means of preventing duplicates at the database level, but it certainly isn't infallible. I consider it the first line of defence.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
 
Old April 14th, 2004, 08:46 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you have a business need to identify that Suzy Smith is really the Suzy Smith that owes your company $850.43, and you get a Suzy Smith person walk into your business needing services from you, and you ask if this Suzy Smith is a current customer and gets re-entered into your database, how will sorting records and deleting duplicates help you determine that this Suzy Smith is the one that owes you monies.

Your safest bet is to use a SSN if your business is small enough, provided that you can comfirm the SSN with a Social Security Card. An SSN can cause problems, you will get duplicate SSN values at times. Universities have this problem because of foreign students or other foreign students who do not have or can not give a valid SSN because of tax fraud reasons, will give any number that comes to mind, leaving room for duplicates.

Go to your bank and ask for your balance and use your SSN to get that balance and you may get two people who are using your SSN and you do not even know it.

Most small businesses will not have this problem, but, people are not honest or they are paranoid to give you their SSN, so they may use a fake one.

An autonumber field does not tell the database table that this record is unique, it only tells you in which order a record was entered.

If you are assigning your customers a customer number, (you should) this would be the primary key for your table. This number could be a combination of last 4 of ssn and parts of their name or something that makes sense to your business.

If you create a multy column index that is unique, you could run into problems when you realize that the new Suzy Smith with the phone number of 555-555-1212 is not the same as the previous one or may be the daughter of the other Suzy Smith.


I would create a smart numbering system for my customers and assign them a PK based on that numbering system. That way if there is ever a problem I can somehow break the rule and change the new entry of a PK and still be able to have two Suzy Smiths with the same phone number, instead of locking my database permanently making the users unable to ever enter another Suzy.




Sal
 
Old April 14th, 2004, 09:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Given that it's a patient system, which deals with admissions as well, here's my suggestion (it's what happens at all the hospitals I work at)
Have one table which has the patients' details
Have another table with the admission tables, so you'll have:

Patient Table
  • Medical Record Number
  • Given Name
  • Last Name
  • Date of Birth
  • etc
Admission Table
  • AdmissionID
  • Medical Record Number
  • Admission Date
  • Admission Type
  • Blood Pressure
  • etc

Given that patients can have multiple admissions, such as emergency, inpatient and outpatient, this allows for such things.

Given that these hospitals deal with tens of thousands of admissions a year, it's natural that there will be some mistakes made. However, the way it's overcome here is:

The patient table has Medical Record Number (which I'll now abbreviate to MRN) as the primary key.

If a patient is admitted who has already been admitted, but is given a new MRN, there's a problem. When that problem is found, they have a different field in the patient table "UseMRN" altered.
Say John Smith was admitted once with a MRN of 123456.
Then, at another time, admitted with a MRN of 987654.
He would have 2 records in the Patient table, with the 2 different MRNs, BUT, would have 123456 (the correct MRN) in the UseMRN for both records.
This way, we can tell that it's the same patient, even if there are 2 records in the table.
So to change the format of the Patient table I mentioned above, we'd have something like:
Patient Table
  • MRN
  • UseMRN
  • Given Name
  • Last Name
  • Date of Birth
  • etc

I am a loud man with a very large hat. This means I am in charge
 
Old April 14th, 2004, 10:48 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

With Access it is possible to set an autonumber programatically at the time of record creation, however, the purpose is to be both a means of uniquely identifying a record and of joining tables in an efficient as possible manner.

Generally speaking, something like a SSN, though numeric, is assigned a text datatype for the simple reason that it is not intended for mathematical operations. Since Access 2k, each character takes 2 bytes of storage and LAN traffic. An autonumber such as 1223212786 or -1143442887 requires exactly 4 bytes to store and transfer even though the number is made of several more digits. An autonumber has another very important feature, it works without any programming. Numeric keys are fundamental to making Access work efficiently with larger numbers of records.

Imbuing an autonumber field with meaning detracts from the intended role of meaningless record identifier but it can be done by seeding the number to start at something like 10,000,000 and allowing Access to assign numbers normally continuing above the seed but run code to set records at specific numbers in various neaningful ranges below that number.

If you create a unique index on FirstName, LastName, DateOfBirth and a byte field with a default value of zero, or just FirstName, LastName and byte field, it will give users the option of entering up to 255 people with the same name and birthdate or just name by setting the byte field to a number other than the default and it will enforce the rule at a database level yet still allow an entry person to affirm that the name isn't a true duplicate and enter a record that would otherwise be a key violation duplicate.

My experience has been that the most common reason for duplications is misspelling. To help prevent such duplications that can not be caught by a unique index, it is useful to employ something like a Soundex algorithm to pop a list of likely duplicates at the time of entry of new records and require the user to dismiss a modal popup displaying a list of duplicate candidates. Less effective is to use a combo with a not in list procedure for entering names because it gets users in the ballpark if you drop the list on focus. This is not as effective because of spellings or presence/absence of middle initials or middle names but at least it helps prevent some duplicates.

No matter what you do, miskeyed entry duplicates are likely to arise in a multiuser environment so a means of identifying and resolving duplicates remains necessary.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help! Setting PK value at runtime! Darrell86 SQL Server 2005 6 November 20th, 2006 01:33 AM
Access ADOX - delete PK Derek_05 Access VBA 7 January 4th, 2006 03:32 AM
Access ADOX - delete PK Derek_05 Access 0 December 14th, 2005 10:56 AM
importing from flat file PK problem olambe Classic ASP Databases 3 October 18th, 2004 04:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.