Results 1 to 4 of 4
  1. #1
    Leonidsg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    68

    Form and Subform design question

    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

  2. #2
    wrkadri is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    29
    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:
    Click image for larger version. 

Name:	Projects and Activities.png 
Views:	23 
Size:	34.4 KB 
ID:	11458

    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

  3. #3
    Leonidsg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    68
    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

    Quote Originally Posted by wrkadri View Post
    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:
    Click image for larger version. 

Name:	Projects and Activities.png 
Views:	23 
Size:	34.4 KB 
ID:	11458

    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

  4. #4
    wrkadri is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    29
    Glad this worked.
    Good luck

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Autonumber form and subform question
    By Helen269 in forum Forms
    Replies: 1
    Last Post: 02-05-2012, 11:27 AM
  2. Really stupid Form/Subform question
    By kathi2005 in forum Access
    Replies: 9
    Last Post: 11-04-2011, 10:17 AM
  3. Replies: 1
    Last Post: 10-28-2011, 01:57 PM
  4. Form Design Question with DLookup (newbie)
    By wongc61 in forum Access
    Replies: 4
    Last Post: 07-08-2011, 03:22 PM
  5. Form Design Question
    By mmori in forum Forms
    Replies: 0
    Last Post: 02-21-2011, 04:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums