Results 1 to 12 of 12
  1. #1
    GonSantos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2019
    Location
    Portugal
    Posts
    6

    Patient Database, linking a patient to several diseases

    Hi everyone,



    I am new to ms access and I'm trying to create a database from scratch to use in my medical practice. As one would expect I've come across several issues which I was able to solve from forums and such, yet right now I am having a problem which I can't seem to find the best solution and I would very much appreciate your help.



    The database refers to 200 inpatients in a 3 month span and so far I've created two tables in which I organized the information:



    »» Table 1: I called it Demographics Data and It states the patient first, middle and last name, his/her age, sex and so on, like this:



    ID (auto number) FirstName Middle Name Last Name Age Sex

    1 John Brewer Smith 45 Male



    »» Table 2: I called it Hospitalization Data and I put the date of admission, date of discharge, duration of hospitalization, patient's status at discharge and so on, the structure being the same as table 1. Also, I created a relationship in which ID in table 1 relates to ID in Table 2, so that I can create a form and a subform in which I am able to introduce all the information to populate my tables at once.



    The third table is the one I am having trouble with. It will state each patient past history. As you may guess a given patient will have various past diseases and I need to create a table that states all of them and relates them to the patient in table 1. My first issue is the table design. Should the table state the several diseases like disease 1, disease 2, disease 3 and so on or is there a better solution?



    Finally, I need that this table be related to the patient table so that I can fill the above mentioned form with each patient information.




    Again, I would very much appreciate your help.

    Gonçalo


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't understand what you mean that table 2 has "the structure being the same as table 1". Doesn't seem like it could. To your question, no, you don't want fields like disease1, disease2. The concept is called normalization. You'd have a related table with fields for the patient ID, the disease, and any other pertinent data.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Gonçalo,

    You might want to take the time to write a description of your medical practice for users and yourself. You know the info better than any reader. You can start at the 30,000 ft level and gradually add details. You will surprise yourself with some of the logic and self-questioning you will go through to put thoughts to paper that clearly communicate what it is that your database is intended to support. The important thing about such a description is that it sets out your requirements and serves as a reference document. I'm sure you will find parts of the project that can be done in "sub projects" as you set priorities on what must be done now and next. And all the sub projects will have to fit together, so that reference document will be key.

    Bottom line: Database isn't just buying the software (although some marketing would have you believe that). It takes planning, knowledge of concepts, some prototype/model to "test ideas and approaches", some testing data and scenarios to ensure the model addresses requirements and leads to a blueprint. With all of that ammunition, it's time to start work on the physical database.

    Here's a link to several relevant articles that you may find useful.

    Draft Doctor's Practice Datamodel from Barry Williams' site.

    Good luck with your project.

  4. #4
    GonSantos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2019
    Location
    Portugal
    Posts
    6
    Thank your for your fast reply. What I meant when I said the structure of table 2 is the same as table 1 is that the design of both tables is similar. I'll write down table 2 so that you can understand what I mean:

    ID | AdmissionDate | DischargeDate | DepartmentOfOrigin | HospitalOfOrigin | DepartmentOfDestiny | HospitalOfDestiny | DischargeStatus

    I created a relationship between ID in table 1 (primary key) and ID in table 2, checking the referential integrity box.
    Then I created a form using the form wizard and included all the fields in both tables except the IDs of table 1 and table 2. When asked about how I wanted to see the data, I ticked the By Table 1 Demographics Data and The Form with Subforms option. This way I was able to fill all fields in both tables and the ID field of table 1 was the same of table 2, which means table 2 is correctly linking its fields to table 1, am I right?
    Now, for table 3 I am having a problem with the design because for each patient in table 1 there will be more than 1 diseases a.k.a. diagnoses, for instance, a given patient might have hypertension, diabetes, dyslipidemia and more diagnoses at the same time. So, how should I design this diagnoses table? And afterwards, how would I link it to table 1?

  5. #5
    GonSantos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2019
    Location
    Portugal
    Posts
    6
    Thank you for your reply orange. I did a quick draft of what my database should look like but maybe I need to spend more time planning it.
    Also, I"ll definitely check out the links you sent.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by GonSantos View Post
    Now, for table 3 I am having a problem with the design because for each patient in table 1 there will be more than 1 diseases a.k.a. diagnoses, for instance, a given patient might have hypertension, diabetes, dyslipidemia and more diagnoses at the same time. So, how should I design this diagnoses table? And afterwards, how would I link it to table 1?
    You'd have a junction table that would have the patient ID from table one and the disease (or disease ID presuming you have a "diseases" table). If I have 3 diagnoses, I have 3 records in that table. The link on normalization should show this. It's a many-to-many junction table. One patient can have many diseases, one disease can be had by many patients.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    GonSantos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2019
    Location
    Portugal
    Posts
    6
    Ok, I get what you’re saying about the diagnoses table where I’ll put all the diagnosis that I’ll use in my project. And I get the many to many relationship. But about that other table that each record is a disease does that mean that I’ll have to create a table per patient? Can you give me a more precise explanation?

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    mtm with subform-davegri-v02.zip
    Here's an example of many-to-many, with forms and reports.
    The example is for members and events. Your case would be patients and diseases.

  9. #9
    GonSantos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2019
    Location
    Portugal
    Posts
    6
    Quote Originally Posted by davegri View Post
    mtm with subform-davegri-v02.zip
    Here's an example of many-to-many, with forms and reports.
    The example is for members and events. Your case would be patients and diseases.
    I used your examples and it worked perfectly with my database. Thank you davegri!

    Now, about the form (I know this is not the right section to ask this but here it goes ), as I said in the first post I have another table for the inpatient data [Inpatient Table] that contains the date of admission, date of discharge, and so on. If I'm not mistaken, this is a one to one relationship, since for any given patient there is only one date of admission, one date of discharge, etc. My question is, what is the easiest way to create a form that contains the patient data [Patient Table] and the in-patient data [Patient Data], since I already know that the way to fill the diagnosis information in the form is through a junction table.

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    If I'm not mistaken, this is a one to one relationship, since for any given patient there is only one date of admission, one date of discharge, etc.
    Nope, there can be several admissions/discharges for a given patient.
    Try this.
    Have a main form for table1.
    Create a form for table2.
    Put a tab control on the main form.
    Put the table2 form on the first tab.
    Put the patient/diagnosis form on the second tab.

  11. #11
    GonSantos is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2019
    Location
    Portugal
    Posts
    6

    Question Form yet again

    Quote Originally Posted by davegri View Post
    Nope, there can be several admissions/discharges for a given patient.
    Try this.
    Have a main form for table1.
    Create a form for table2.
    Put a tab control on the main form.
    Put the table2 form on the first tab.
    Put the patient/diagnosis form on the second tab.
    You're right about that one, a given patient can have multiple admissions. I guess I was being lasy (I even thought of joining the information in the hospital stay table [table 2] to the patient demographics table [table 1] just for the sake of easy form filling ).
    One more question about the form: I read that for the sake of normalization one should avoid the lookup fields. Instead one should create a combo box that collects the possible values from another table. In my database, in table 2 in the hospital of origin field (the facility where the patient comes from), I have a finite list of hospitals and I want to automatically fill the hospital of origin field without having to write multiple times the same hospital name. The lookup field for a beginner like me seems to be the easiest way, but should I create a new table with the list of hospitals to use the combo box method for the sake of future wellbeing of the database?

  12. #12
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Yes, I would create a hospital table. Then if new hospitals are added they would automatically appear in the combobox dropdown on the forms.

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

Similar Threads

  1. Replies: 27
    Last Post: 12-13-2015, 04:05 PM
  2. Replies: 6
    Last Post: 08-25-2015, 12:01 AM
  3. Patient database
    By Addanny in forum Access
    Replies: 4
    Last Post: 10-02-2013, 06:59 PM
  4. Help With Patient Tracking Database Design
    By wkenddad in forum Programming
    Replies: 2
    Last Post: 04-25-2012, 09:15 PM
  5. Patient Database
    By labadee in forum Database Design
    Replies: 1
    Last Post: 06-27-2010, 10:53 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