updating an identifier

[Spider]

Hi all,
This is probably very simple - but I can't seem to figure out a way to do it or find an example.

I have a field in my table (MRNum) that is supposed to be a 'unique' patient identifier in the order entry system for our hospital . It is not my unique identifier for the table - I use an auto number (PtID). The MRNum is for the users of the order entry system to identify a patient -then list all of the patient encounters (hospital visits).

In one of my databases, they wanted to use this MRNum as well, so that they can search for the patient and list the encounters (a specific procedure).

The problem is, this MRNum is supposed to be unique, but there are patient registration errors that somehow allow a duplicate MRNum -imagine that!

We have 5 hospital facilities, each has facility ID in this same hospital order entry system.
In my database, I would like to do an update query - or anything else that might work - to add a facility number to the existing MRNum for each facility so that the users can find the correct patient when the patient is re-admitted.

The MRNum looks like this: 541254
I would like it change the several thousands MRNum(s) to 1-541254 for facility one, 2-541254 for facility two etc...

MRNum is a text field.

I'm not very good at writing code, can this be done with an update query?
Thanks!
Vicky

[HansV]

How do you know to which facility a patient belongs? Or do you have a separate table or even database for each facility?

[Spider]

I have a FacilityID in my database. The Data Entry form in my database defults to their specific facility when they are entering data. it is also facility 1, 2, 3 ect.

The hospital order entry system has a financial account number for each hospital that is identified with a 1, 2 or 3 prefix.
it loks like 101115311 for hospital 1, 201115311 for hospital 2, 301115311 for hospital 3. But, these are visit specific so they change with every encounter - so it would not work to find the patient - only to find a particular visit. Our other computer systems also use the 1, 2, 3 for facility identification.

[HansV]

OK, but if you want to update existing records, you can't look at the FacilityID you're currently using, can you?
Is there a field in the patients table from which we can get the FacilityID for each patient?

[Spider]

I hope I'm understanding the first sentance.
The FacilityID is in the Encounter table. Each patient encounter could be at a different facility.
I can only see the FacilityID in a query with the Patient and Encounter tables.

I have used an update query many times - but never to append a prefix to existing data.
Hope that helps,

[HansV]

One patient can have records with different facilities in the Encounter table. So how should we decide which facility to use in the Patient table?

[Spider]

I'm confused :)
The MRNum is in the Patient table - I need existing MRNums to be changed to include a prefix. After that data is changed, the user will type in the prefix with the MRNum from now on. So instead of typing the MRNum 223344 as they do today, they will type 1-223344. We need the '1-', or '2-' or '3-' added to the existing numbers in the Patient table, then the user will type this in -in the future -it will be part of the MRNum. The prefix happens to be the facility ID - but not the database facility ID.
It would not be important - but this MRNum is what they use to find their patients. They understand that they will be placing the facility number in front of the MRNum in the future.

The dulplicate MRNum problem happens when the patient has been at multiple facilities.

I'm not sure that this is the best way to remedy the problem, but it was what everyone agreed to.
If it is possible to do an update query, I can link the Patient and Encounter table to get the Facility for the prefix.

[HansV]

Let's take an example. There is an existing MRNum 223344 in the Patient table.
This patient has visited hospital 1, and on another occasion hospital 3. So, there are (at least) two records with MRNum = 223344 in the Encounter table - one with FacilityID = 1 and another one with FacilityID = 3.
If we create a query based on the Patient and Encounter tables, joined on MRNum, we also have two records with MRNum = 223344, one with FacilityID = 1 and another with FacilityID = 3. So we don't know whether we should update MRNum in the Patient table to 1-223344 or to 3-223344.
Or am I misinterpreting what you want?

[Spider]

I printed this and am taking it to my 2:30 meeting to think about -instead of paying attention to the meeting.
so... I will give this some thought before I answer.
Thank you for your patience!

[Spider]

I think I understand the confusion. You do understand exactly what I need- but the MRNum is not the primary key and is not in the Encounter table. The query would be linked on the PtID and EncounterID where the FacilityID = one of the facilities at a time for the update. The MRNum is just a text field in the Patient table that they search on in my database - and they can also use it to reference this patient in the history of our order entry system when they need to. Will that help?

[HansV]

I'm afraid I still have no idea how we can decide which prefix to use for a given MRNum.

[Spider]

You are right (as usual :)
I am going to discuss other options with them. It doesn't really matter what facility they were in -it only matters that it is a second patient with that MRNum.
Since the duplicate MRNum is an anomaly anyway, I don't see why they cannot just add MRNum = 223344, MRNum = 223344-2 (the 2 meaning the second MRNum, not FacilityID) if it happens. They always search before adding a patient as it is. It only happens 5-6 times a year.
Thanks again for helping me think this through.