Relationship Dilemma? I think??

[Palomino33]

Let's say: I've got 100 patients. They are in one table.
Let's also say: I've got 50 patient rooms with beds A & B set up in another table...like 1A, 1B, 2A, 2B, and so on.



I want to assign the first patient to bed 1A. 2nd patient to 1B (don't want 1A to be available if it's in use)....and so on.

At the end of the day, I want to print one report by patient that says:
Angelina Jolie - 1A
Brad Pitt - 1B

And...I want to print another report that tells me what beds are vacant. I don't know if I'm brain dead...or just not smart enough to figure this out, but would appreciate any help anyone could give. Thanks a Million!

[alansidman]

Show us the table layouts. How are you relating the names in Table 1 with the beds in Table B. Need to understand your schema in order to give you an answer.

[goodguy]

No, you are not brain dead; it's just developer's block which strikes each one of us at some time.
In addition to your tblPatients and tblBeds, make a junction table tblPatientBeds with foreign keys from each table.
Create AdmissionDate and DischargeDate fields in tblPatientBeds.
When you assign a bed to a patient, create the relevant record in tblPatientBeds.
Update a status field in tblBeds to "Occupied" and remember to set it to "Vacant" upon discharge.
Now make your reports using saved queries.

[Palomino33]

Hi Alan & Goodguy!
Thanks for reading and offering your suggestions. Can't wait to try them.

For you both, the database I've written for work is very complicated with many objects and relationships. But for the purpose of getting this bed thing down pat....I have a simple experimental database here at home. Once I get the joins, relationships, or whatever I'm missing right, it will be not problem to write into the work database.

For Alan: My simple database at home has two tables.
1. Patient Table - two fields (1) Patient Name, (2) Bed Assignment. Field 2 is a lookup field for Table 2. I just choose the bed assignment.
2. Bed Table - 2 fields, list of beds. Field 1 is Rooms, with data being rooms 1-25. Field 2 is beds. An A & B (two separate records) for each room.

This is the way I've done it for a while now, but I know it can be better.
Cons: I could overbook a patient to an occupied bed.
Pros: I can get one of the reports that I want....one that Groups patients by room, and gives me their bed assignment. So, as long as I have my room report that shows me the # of patients per room, I can choose a bed/room that has an opening.

BTW, I work at a substance abuse/mental illness treatment center. And...I need treatment myself for OCD because something like this keeps me up night after night

[Palomino33]

And to Good Guy,

You have no idea....I tried a junction table the other night....something told me that was what I should be doing, but had never done one...so, just couldn't get it right. But I will try your suggestion and try again. Before doing so, I think I'll read up on junction tables just a little bit more, well, actually, I did junction query.

Hey, I'm off to try a junction table. Will report back to you guys soon. I want to say thank you so much for your offerings. I will be in this forum and will try to help others as much as you guys are trying to help me

[goodguy]

thanks.

[Palomino33]

I'm still not getting it. Hey, just a thought, I've got skydrive Would either of you want to pull it from there to look at it?

[Palomino33]

thought I could attach it to this post, but it exceeds the file limit but could put it on skydrive...and with your email address could list you as to share it.

[alansidman]

Do a compact and repair and see if that is small enough. If not, then zip it and attach it.

[Palomino33]

Hi Alan,

I've attached my test db. I don't know why I'm not getting this. Thank you so much for your help.

[goodguy]

I have made some changes including to your primary keys and queries.

[Palomino33]

Hi Good Guy,

Thank you so much for looking at the file for me. I love what you've done with the file. What I love most is seeing how other people think. Now that you've seen this and can see what I've done, maybe what I'm asking will make more sense.

Here are a couple of questions/comments I've got.

1. Need an empty bed query. Just need to print a quick list of empty beds. I think that comes from an outer join.
3. I don't understand why there are 12 patients in tblpatients...and 5 in tblpatientbeds. Do you suggest the patient name be entered twice?
4. I love your forms

Thank you so much. I am almost at the point of having no confidence at all in myself, over my lack of understanding of this one issue. I look at work I've done in the past (been while) and know it is way more complex than this...and wonder what has happened to me! Remember that phrase use it or lose it! Well I'm thinking I've lost it

[goodguy]

I have included the vacant beds query and filtered the beds combo to vacant only for new admissions. To answer your question, What do you do when a patient is readmitted?

[Palomino33]

Hey Good guy,

I really wish you could see the db we use every day at the outpatient facility.

The hospital has an accounting program (DOS based....and is 30 years old....go figure) that generates a unique medical record number, and of course that number identifies the patients, forever.

Now, not all patients come to the outpatient program where I am. Relatively speaking, only a few wind up coming to us...so in my patient data table....my primary key generates and IOP# that we use to identify that in our program. It's their IOP#. Currently, it's a 3 digit number....we'll have a few more years before it goes to 4 digits. In answering your question with a new admission, they get a new record....and their Medical record number stays the same, but they get a new IOP#.

Hope this helps ~ I'm downloading the zip file now, can't wait to see it.

Suzanne

[goodguy]

One thing I have learnt in my 22 years in the software industry is that almost every problem that seems too complicated can be solved with creative redesign. So don't worry, post as much detail as you feel you can safely provide without compromising any confidentiality policies and we will try to craft an imaginative workaround. Wishing you all the best luck.