Combine Multiple Fields with a Twist

[SMay]

The goal is to create a unique Patient ID based upon 2 separate fields from separate tables.

I want to extract the last 4 digits of a social security number in a Patient Table and then combine those 4 digits with the PhysicianID (PhysicianTable) to create a unique PatientID which will be the new permanent ID.

Ex.
Physician Number = 1001
Patient SSN = 111-22-9999
Patient Last 4 = 9999
Final Combination = 10019999

Thanks in advance.

 

[MSAccessRookie]

The goal is to create a unique Patient ID based upon 2 separate fields from separate tables.

I want to extract the last 4 digits of a social security number in a Patient Table and then combine those 4 digits with the PhysicianID (PhysicianTable) to create a unique PatientID which will be the new permanent ID.

Ex.
Physician Number = 1001
Patient SSN = 111-22-9999
Patient Last 4 = 9999
Final Combination = 10019999






Thanks in advance.

1. The Physician Number speaks for itself. If it is a Number instead of a String, then you will need to look up the cStr() Function.
2. The SSM Must be a String, so look up the Right() Function to get what you want there.
3. Concatenation is performed by using the "&" character. (ResultString = FirstString & SecondString)

See what you can do with this assistance and get back if you need more

 

[gemma-the-husky]

before you do this, why?

a) why do you need a patient number like this
b) how is this guaranteed to be unique
c) why not use an autonumber - you still have the doctor ref and social security numbers avaialble if you need them

 

[dkinley]

before you do this, why?

To add to Gemma's question - the premise is, are you doing this to act as a primary key for your table? Or is there already a key and you are doing this for "human interfacing" of the record? This second aspect is something for readability and usability by a human - the first is for the application to maintain it's integrity.

-dK

 

[Mike375]

Plus if it is only used for display purposes then you only need the result from the calculated query field.

I am petty rough and ready with Access when it comes to breaking rules and doing work arounds but even I would not want a primary key that is on the basis of getting data from two tables

 

[boblarson]

I am petty rough and ready with Access when it comes to breaking rules and doing work arounds but even I would not want a primary key that is on the basis of getting data from two tables

Looks like there's hope for you yet Mike

 

[dkinley]

Hehehe ......

 

[SMay]

Plus if it is only used for display purposes then you only need the result from the calculated query field.

I am petty rough and ready with Access when it comes to breaking rules and doing work arounds but even I would not want a primary key that is on the basis of getting data from two tables

Thanks for all of the replies. To answer this question. I am doing this more for Human Interfacing of the record.

But i thought that while i have the number it could then become the Primary Key for the Patient.

Also to clarify, the Physician is My customer and then the Patient is My Customer's Customer. We work with multiple physicians who have multiple patients. That is why I am creating the PhysicianID+Last4. The primary key for the patient will always be the SSN however i thought that the PhysID+Last4 would be a secondary way to index and create a logical corporate ID for the patients regardless of physician.

If you have a better suggestion . . . I am all ears.

Finally, MSAccessRookie's idea was FANTASTIC.

 

[dkinley]

To answer this question. I am doing this more for Human Interfacing of the record.

But i thought that while i have the number it could then become the Primary Key for the Patient.

Then it seems you are good-to-go. Use it for your hard copy filing, etc, but let Access do it's thing with an autonumbering field for a primary key. It will not hurt you to let it do this but will benefit you because it will probably save you alot of harm down the road.

-dK