Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    52

    Unanswered: Health record for family,, question about tables

    Hi,
    I have just started creating a program to keep track of my family's health records ..I want it to show date visit to which doctor and what was the illness and prescription ..
    I 'll write what tables I have and I want to know is this correct or am I doing something wrong?

    Table Patient
    Patient ID (autonumber)
    Patient Name
    DOB


    Table Bill(or Appointment)
    BillID
    PatientID (should I write Patient Name??) and should I set it to text or autoNumber
    Date
    Sickness
    Prescription
    DoctorID (or should it be Name?)
    Notes
    Invoice

    Table Doctor
    DoctorID
    DoctorName
    Phone#
    HospitalName
    Major
    Note

    Table Major
    Major ID
    Major Name

    First question, how should I make relation ship between
    table BILL and table Patient is it one to many? I so confused because each bill should have only one patient and one doctor but in general each patient can go to many doctors and viseversa. I have same question for all tables,

    Second question, when I link tables, should the table for example Patients have the field Doctor ID or Doctor Name? if it is Doctor ID should I set to text and then it will retrieve it from the original table or to number or what??

    In addition, is it okay to add table Major?
    I created it ,because I want to have a form that allows me to go to each major, for example, when I my child has ear infection , I want to open a form called ENT and there, I can see records of last visits to ENT + enter data for the current visit. Is this table necessary in order to achieve this point ?or should I delete this table as there will some way to create a form doing what I want using this field (like a query?)

    I hope someone can advice me since it has been very very long time since I studied Entity Relation Ship Diagram and I am so confused now ...

    Thanks in advance

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The two tables would be related one-to-many on PatientID. If PatientID is an autonumber, it would be Long Integer in the bills table (and you would not store the name). Same for DoctorID and the doctors table.

    I'm not clear on what the Major table is for.

    Other thoughts; both sickness and prescription should be related tables, as presumably the possibility exists that there could be more than one of each. In other words, in one appointment I might get diagnosed with both an ear infection and the flu. I might get prescribed an antibiotic and something else.
    Paul

  3. #3
    Join Date
    Aug 2006
    Posts
    52
    Paul
    Thanks a lot for the help
    regarding sickness and prescription, it is okay to make it memo instead of creating tables?
    regarding major, when I open the main form, i want to see different buttons, one for ENT form (it will show all records of ENT visits) and another one for Dentist (it will show all records of Dentist) etc...Can i do it with a form based on query without using this major table??

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is it okay? Sure; it just wouldn't be a normalized structure.

    As to opening a form restricted to certain types of entries without that table, sure, as long as the main table contains the appropriate data (like ENT). You could use this technique behind the buttons:

    Open a second form to the record

    But hard-coding the condition, like:

    DoCmd.OpenForm "SecondFormName", , , "Major = 'ENT'"
    Paul

  5. #5
    Join Date
    Aug 2006
    Posts
    52
    Thanks a lot Paul, I appreciate your help
    I will think again about creating separate tables for sickness and prescription
    THanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •