Unique Identifier

[Santander]

Hello all,

I've looked on the net, and can't seem to find an answer that best suits my needs. I'm developing a package for a Doctor.
I need to use a unique identifier for all records. The primary screen will be for Adding Patients. From this table I will need to update small bits of info to various other tables. Maybe I'm being too analytical when it comes to the unique key, but I would like to know how to unstring parts of 3 fields and combine them into a new field (the Index key). All of this would be done when the user clicks on 'Save Record' at the end of the form.

Example: Patient Name is John Does, with a SSN of 111-11-1111. The key I want would be first 4 letters of FName, first 4 letters of LName, and last 4 digits of SSN for a key that would look like johndoes1111. That should fairly well eliminate the possibility of 2 patients with the same name.

I know how to do this in theory, from various other languages I've used, but not a clue in Access 2007.

Am I being too complex with the key? Or is this workable?

[PhilOfWalton]

The simple way is

Left(FName,4) & left(LName,4) & Right(SSN,4)
 

However if there are less than 4 letters in a name - John Doe for example you get JohnDoe11121

So you might want to pad it with spaces so that you always get a 12 letter result.

Left(FName & "    ",4) & left(LName & "    ",4) & Right("    " & SSN,4)
 

"Fairly well eliminate the possibility 2 patients with the same name." is not a good option.

I assume you have a primary Key for your table - PatientID as an autonumber which is the foreign key for the other tables you mentioned

Phil

[Santander]

Can't believe I'd forgotten how to do that. Thanks!
Can a table have more than one key? I don't recall, and the programmer's reference I have is vague on the issue. Using the statement above:
PatID = Left(FName & " ",4) & left(LName & " ",4) & Right(" " & SSN,4)? Would this work in assigning the Patient ID? I want the data for the field created when the user clicks on the 'Save Record' key.

Was thinking about it. The last time I used Access and VB, was 15 yrs ago.

[PhilOfWalton]

I strongly recommend using a PatientID (AutoMumber) as the primary key.
The PatID mentioned above can also be set as a Key (No Duplicates) if you wish.

Is this number going to be of any use to anyone? If I wanted to look up a record, I would select the patient's name from a Combo Box.

Incidentally inn my original reply I had 4 spaces after the name, you only showed 1 space in your reply.

Phil

[jforbes]

I would agree with Phil, AutoNumber would be your best route. My biggest concern for this structure for a Primary Key is that you cannot guarantee that you would not get a repeat. If you can't be 100% unique, then you are better off using an AutoNumber and then creating a separate field that is filled in as you described.