Results 1 to 4 of 4
  1. #1
    ExterminatorJeff is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    1

    Question on how to structure a database

    Hi



    I'm currently working on setting up an Access database for a health related business but I'm having some problems figuring out how to structure the database. The business has been using an excel spreadsheet to store all its client data for the last 5 or so years but this has gotten too big to be effective. Currently I have a table called clients which is basically all of the information imported from the excel sheet. What I want to do is split parts of this table into other tables to make the information easier to read. Specifically I want to create new tables for Conditions (health conditions that clients may have) and Medications. At the moment these are stored just as strings in single fields and these is no structure or consistency to them. An entry for a client might contain only one condition or it might contain ten, all writen in a long senqence.

    How can I structure my database so that a client can be linked to many distinct conditions? I need to be able to add new conditions to the Conditions table and have them automatically able to be linked to a client. I'm a bit confused about what I'm doing here which is probably obvious. I want to use a form to enter the data which will have a tab for both conditions and medications. These tabs will have a bunch of tickboxes for each condition or medication. The form operates on one client at a time so it should update to show which clients have which conditions and medications.

    Hopefully I've explained this well enough

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Consider

    table PatientConditions
    PatConID (primary key)
    PatientID (foreign key)
    ConditionID (foreign key link to table of medical conditions with diagnosis code and other info)

    table ConditionsMedication
    PatConID (foreign key)
    MedicationID (foreign key)
    Dosage
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    A few thoughts :

    A PATIENT can have one or many CONDITIONS.
    One PATIENT CONDITION can be treated with one or many MEDICATIONS.
    The TREATMENT or TREATMENTS can take place over a period of TIME.

    tblPatients - Master Table of Patients
    PatientID - PK
    .......

    tblConditions - Master Table of standardized List of Conditions
    ConditionID - PK
    .......

    tblMedications - Master Table of standardized List of Medications
    MedicationID - PK
    .......

    tblDoctors - Master Table of Doctors
    DoctorID - PK
    .......

    tblPatientsConditions
    PatientsConditionsID - PK
    Patient_ID - FK
    Condition_ID - FK

    tblPatientsConditionsTreatment
    PatientsConditionsTreatmentID - PK
    TreatmentDateTime
    PatientsConditions_ID - FK
    Medication_ID - FK
    MedicationDosage
    NextCheckUpDate
    Doctor_ID - FK

    Thanks

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to June7's and recyan's good comments and advice, you should work on your table structures before getting too deep into programming.
    Getting the tables and relationships right will make the programming and coding much easier.

    Some things to research - Normalization and junction tables.
    Also, since it is possible for the same patient to have a recurrence of a condition, you may wish to consider a Date to be associated with a PatientCondition.

    Here are some tutorials that illustrate design concepts.

    http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

    http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

    http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

    http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

    http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

    http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

    http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

    http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

    http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

    The first few topics are well worth the read.

    http://www.rogersaccesslibrary.com/forum/topic238.html

    Good luck with your project

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

Similar Threads

  1. General Access Structure Question
    By caw442000 in forum Access
    Replies: 6
    Last Post: 10-13-2011, 06:49 PM
  2. Relational Structure - Beginner Question
    By CrazyFileMaker in forum Access
    Replies: 2
    Last Post: 01-02-2011, 11:28 PM
  3. Structure/Relationship Question? Please Help...
    By Imgsolutions in forum Access
    Replies: 2
    Last Post: 06-27-2010, 09:52 PM
  4. Database Structure | Best Practice Question
    By davidson12 in forum Forms
    Replies: 0
    Last Post: 11-05-2009, 03:29 PM
  5. An import question above the table structure
    By Shag84 in forum Import/Export Data
    Replies: 2
    Last Post: 08-20-2009, 12:21 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