How to create relationships between tables for this situation

[Bradex]

I am new to access and am having problems understanding relationships between tables. I tried my best to read help articles but I could not fit them into my situation. This might be a long & tricky question. I apologize for that. I thank the reader for his/her patience and time. Now the question...

I am trying to create a database for a hospital. Patients get admitted to the hospital, get monitored every day and then get discharged. To collect their data I made 3 tables.
1) Patient entry table - Has background details of the patient when he comes first to the hospital
2) Daily monitoring table - For entry of daily parameters (heart rate, temperature, blood pressure, etc.)
3) Patient discharge (exit) table - Has certain other parameters which are checked at his exit

Of these Tables 1 & 3 are filled only once i.e. at entry and exit while table 2 is filled daily for a patient.

Each patient is given an unique hospital ID.

I had made 3 separate forms (for each table) with the fields from the tables for data entry (as the fields will require labels to explain certain details for an inexperienced user who tries to enter data). Forms are meant for data entry... Am I right?

My questions are
1) How will I link the entries in the 3 tables to a single patient (i.e. to a single hospital ID). Should I enter the hospital ID for each form every time and then match the same ID in the 3 tables. Is there a way to easily link all data of one patient?
2) When I enter daily data of a single patient, I am not allowed to enter details of a patient again because the hospital ID is the primary key. I can circumvent this problem by removing the primary key and by entering the day of stay (D1, D2, D3 etc) and the hospital ID every time. But is there a better way?
3) Since the forms were quite big, I had to split them and use them in a navigation form (with entry, daily & exit tabs horizontally at the top and their respective split forms on the left as vertical tabs). For example, the entry form tab has 3 parts on the left. How do I connect these 3 parts to a single patient.



I have attached the databases i have created with this post. Please point out where I am wrong if possible.

My intuition is telling me that I am making a mistake somewhere. If there is a better way to make this database (especially making the tables) please guide me. Any links, suggestions will be greatly appreciated. Again thanks for your patience and your time!

[June7]

First, I don't understand why two patient tables (entry and exit). These tables can have only one record per patient and will always be a record for each patient in both tables. This is all info about patient and should be one table. Now I have concerns about the fields. Will every field always have a value for every patient? What are all the medical sounding (Ceftriaxone, Aztreonam, etc) name fields for? I suspect some sort of child table is called for. Also, you have a lot of Yes/No fields that maybe could be combined as one field. For instance, Transf_Out and Death fields are indicating case resolution. Why not one field with choices of Transfer or Death? Or instead of the Yes/No, wouldn't a date be more informative?

The primary/foreign key fields seem okay but you did not set up Relationships. This is not critical for database functionality but can be helpful when building queries.

To allow patient to have multiple visits you need a table for general patient info such as patientID (primary key) name, address, insurance, DOB, etc. Then you would have a Visits table for visitID (primary key), patientID (foreign key) date of visit, complaint, diagnosis, date of exit, etc. Then you would have the daily data table with foreign key field link to the visits table, same for culture.

Your form needs a main form bound to patient info table then dependent subform and subsubform would be for the visits and daily/culture tables. You used the Navigation form wizard. I don't like the wizards. Not sure this structure is appropriate for your situation. I don't see any way to link the subforms on the navigation tabs to a parent form. I would use the old subform container control to build this structure.

[Bradex]

I have made 2 tables (entry & exit) because the data will not be available at the same time. For example if a patient enters the hospital today, he might exit 2 weeks later. But if you advise me to combine them I shall do it. I agree that all the entry/exit data is for a single patient. I can make two separate forms but a single table...right?

Ceftriaxone, Aztreonam, etc are names of drugs which the patient receives in the hospital. I have to enter the number of days the patient receives each drug. A patient may not receive all drugs sometimes he will receive only 1 or 2 drugs so that i have to leave the rest of the fields blank.

Regarding transfer out/death, i agree with you that i can make it into a single field with an option between the two.

I couldn't understand how I can make a primary key for each day of the patient's stay in the hospital. Can I use the patient's hospital ID (dot) the day of stay? It will look like this 12345.1 (for the patient 12345 on D1), 12345.2(for the patient 12345 on D2) , ....

One more question, due to lack of space in the navigation form I had to split certain forms. So whenever i split a form I should put the patient ID in the form right?

Thanks for the amazing answers!

[June7]

Doesn't matter that data is not available all at once, only concern is if all the fields will eventually have data.

Did you get my point about a patient info table and vistits table and daily table?

The primary key for patient visits could be generated by an autonumber datatype. Set the date field to Index (no duplicates).

[Bradex]

After going through your answer more carefully once again I understood that for visits/daily data/culture I have to create a unique primary key and in these tables i can use the PatientID of the entry form as a foreign key.

But I didn't understand 'set the date field to index'

I am not very familiar with subforms. Will try my hand on it right now. Will mark this thread as solved the moment i succeed.

Many thanks for your help once again!

[Bradex]

Found out indexed field in field properties