Form and Subform design question

[Leonidsg]

Hello,



I have a design question that I don't even know how phrase to be able to search online. I apologize if this has been asked before. I have a table that collects basic patient information i.e. name, date of birth, address, medical diagnosis, allergies, etc. For that table I have a very simple form. My question is: I would like to make a related table which has Surgery Events. Essentially I would like to have a part of the form which lets me input Surgical Events. The issue is a person can have multiple surgeries. Therefore is it best to have a related table with multiple fields for example: Surgery1, Surgery2, Surgery3 OR is it better that each record only have one surgery field and the patient would just have multiple records. It seems that it would be better to have only one Surgery Field because I have no way of predicting how many surgeries one person would have. Also for the sake of normalization it seems like a better idea to have only one field rather than multiple. Secondly I would like to make the form populate the linked field which is the patient medical record number so when I add the surgery it is automatically linked via that MRN. I hope this makes sense. Please let me know if it doesn't.

Thank you very much for the help.

Lenny

[wrkadri]

I have been working on same database but in different field, What you need to do is create table Patient Information with primary key PatientID, and table Surgery Information with primary key SurgeryID.
Then you add the PatientID as foreign key in the Surgery Table with the same data type which in this case should be Number. When you connect [Patient Information].PatientID with [Surgery Information].PatientID you are creating a one-to-many relationship.
Now on the Form, you can create a form which includes PatientID, PatientName, PatientAddress, etc... from table Patient Information, and add a subform which represents the Surgery Information Table.
In this case, when you enter a patient record, in the subform you can add as many surgeries as you want.

Check my database example below:


Mine look like this because I changed the properties of the subform for let's say Table.[Surgery Information]
Link Master Fields PatientID
Link Child Fields PatientID

Hope this works as you want.

Regards

[Leonidsg]

Great this helps a ton, just to clarify I would link the relationship between the tables with the Medical Record Number field forming a one-many relationship.

Thank you,
Lenny

I have been working on same database but in different field, What you need to do is create table Patient Information with primary key PatientID, and table Surgery Information with primary key SurgeryID.
Then you add the PatientID as foreign key in the Surgery Table with the same data type which in this case should be Number. When you connect [Patient Information].PatientID with [Surgery Information].PatientID you are creating a one-to-many relationship.
Now on the Form, you can create a form which includes PatientID, PatientName, PatientAddress, etc... from table Patient Information, and add a subform which represents the Surgery Information Table.
In this case, when you enter a patient record, in the subform you can add as many surgeries as you want.

Check my database example below:


Mine look like this because I changed the properties of the subform for let's say Table.[Surgery Information]
Link Master Fields PatientID
Link Child Fields PatientID

Hope this works as you want.

Regards

[wrkadri]

Glad this worked.
Good luck