Linking records in look-up fields: making the record NOT appear as an option once assigned

[s2stewar]

Context:
Table #1 (A): Tbl_TraumaCodes: It marks all the dates, times, and hospital beds where a medical team is alerted to go treat a patient with a serious traumatic injury.
Table #2 (B): Tbl_Location: Lists the date, time, and location (area of the hospital, bed number) where a patient was with an identifying number.
Table #3 (C): Tbl_Outcomes: Has an identifying number paired with discharge outcomes.



Task: I need to with a reasonable amount of surety, match records in Tbl_TraumaCodes with Tbl_Outcomes. A to C. The only way to do that is to go through Connect B and C through a unique ID, and use the date, location, and approximate time to connect B to A.

Matching B.Tbl_Location and C. Tbl_Outcomes is easy and automatic through a matching query using the identifying number. Matching B. Tbl_Location records with A. Tbl_Trauma Codes will create the link I need.
I designed a look-up table in B. Tbl_Location where the date, time, and location of records from A. Tbl_ TraumaCode appears so that I can match them. However, the times that are supposed to correspond between Table_Location and Table_TraumaCode are not exactly the same. The times are roughly within the same ballpark (usually 30 +/- min).

Problem: I have thousands of records to match. There may only be 10 records on a given day, which allows me to limit the options when I type in, say, July 1st in the look-up table. Not every item in B. Tbl_Location with have a matching item in A. Tbl_TraumaCode, but every record in A should have a match in B. That means I have to match 10 records when there may be 40 extra record to work with. It’s incorrect to assign an item (time) in Table_TraumaCode to more than one item in the Table_Location. My goal is to reduce the potential for human error.

Is there a way to make the records from the look-up table that are already assigned to a record within Tbl_Location NOT display in the look-up field? I thought about drawing the look-up table from a query, but I don’t know how I would create a TraumaCode query that only displays records that aren’t matched in another table. I also don't know if it would impact the previously assigned records.

I avail myself of the collective wisdom and humbly thank you.

[aytee111]

Why is the time on more than one table? Surely this makes this database untrustworthy? Your table structure needs to be normalized - one event, one patient, one bed. The event has a date and time which is tied to a patient and is tied to a bed. The bed has a location. The event has an outcome. Each bit of data needs to be stored only once and all is linked by primary keys, making it all "easy" as you call it. Simple and straightforward.

You also have referential integrity problems, with a record which can be missing in tbl A. There needs to be one entry which would store all the data at one time. You can get rid of tbl B.

You seem to have bed on both A and B, could you not link on this item? Any duplicates, which would be rare, could be further narrowed down using the time.

Can you explain what you are trying to do, why do you need to match these records, is this a one-time function, a user search,..?

create a TraumaCode query that only displays records that aren’t matched in another table

You do this by joining the two tables using an outer join - double-click on the join line and select which option, all records from tbl A, etc. Then in the query select the ID from tbl B and have as the criteria =Null

[orange]

I agree with aytee111's request --please tell us in simple English --no Access or database jargon --
What exactly are you trying to do?

Tell us about a typical occurrence of a trauma --simple, English --just as you would tell an 8 year old.
We need to understand WHAT before we can offer focused advice on HOW.

Good luck.

[s2stewar]

I do research for a hospital that treats many patients with traumatic injuries.

When such a patient comes in, a trauma code is called. This assembles a large medical team that is directed by a trauma doc to take care of a patient. We want to know if giving a trauma doc training to coordinate this team means better patient outcomes. Half of the trauma docs get training, and the other will not. We’ll compare the patient outcomes for a doctor before they got the training and after. We’ll also compare the patient outcomes for a doctor who didn’t get the training over a similar time period.

To do this, I have to assemble, link, and compare data from multiple sources. These are all independent sources.
Data Source 1: Trauma codes. I get an alert every time the medical team is called to assemble. It tells me the date, time, and location of the patient in the Emergency Department.

Data Source 2: Video. We take video of the medical team treating the patient when a trauma code is called. We don’t have video cameras everywhere in the Emergency Department, so not every trauma code has a video. BUT, every video has a trauma code. (I can’t just ask the patient for their ID. It’s illegal. If you’re curious, we use a checklist on the video to score if the doctor did what they were trained.)

Data Source 3: Patient Outcomes. A hospital registry gives me a list of all the patients by a trauma registry ID number (not their name) with traumatic injuries. It tells me about the injury, and how healthy the person was when they left the hospital.

Data Source 4: Location. The hospital will give me a list of everyone with a trauma registry ID and tell me when they came to the hospital, and all of the different places they were at.
Ex.
ID #1234: January 1^st - 12:20p-1:30p: Waiting Room.
ID #1234: January 1^st - 1:30p-3:35p: Room 2, Bed 3.
ID #1234: January 1^st – 3:35-January 2^nd 5p: Room 5, Bed 1
ID #1234: January 2^nd 5pm – Checkout of the hospital.

Task: To know if the training helps patients, I need to know which patient was being taken care of in the video. I know what trauma code and video go together – I make that link. I know everywhere a patient went, so the outcomes and location are linked. Now I have to bridge the gap – for about 4,000 patients.

The location of a patient is generated by a different system that sends out an alert. An alert may go out 30 minutes before a person gets there, saying that a patient is going to Room 1, Bed 5, so get ready. The location won’t actually mark that patient as in that room until they are. An alert may go out 45 minutes after that patient gets in a bed. It takes a human being to look at the locations where a patient was and say that this alert was probably for them.

There are about a dozen trauma alerts a day. I will have two years of data when it’s done. That’s a lot of room for error. It would be great if when a person said, yes, this July 1^st alert at 12:30pm in Room 325 probably matches the patient at 12:45pm in Room 325, that assigned alert disappears from my list of options to assign to the next patient. I wouldn’t want it to be accidentally assigned to the 12:40pm patient in Room 335.

No, I can’t just match by location because the formats are different between the two data sources. Heaven knows that I wish I could.

Not exactly 8 year old worthy, but it is what I could make of it. Thank you for your willingness to help me!

[June7]

Cross-posted https://stackoverflow.com/questions/...94888_44686250