Table Question

[Rabastan]

I am attempting to build a database for patients. So far I have it set up
like this;

tblPatients
patientsID
patientsFIRSTNAME
patientsLASTNAME
patientsPHONE
ect... all the normal info (Its a long list LOL)

Anyway what I need to be able to do is set up a Medication list for them. I
would like the database users to be able to select a drug from a drop down
list have it somehow add that drug to the data base and create another field
for the next drug. As some people take two medications and some take fifteen.
Is there a way to do this without setting up a table that looks like this;

tblPatients
patientsID
patientsFIRSTNAME
patientsLASTNAME
patientsPHONE
patientsMEDICATION1
patientsMEDICATION2
patientsMEDICATION3
patientsMEDICATION4
patientsMEDICATION5
ect........

I see two problems with this, if a patient only takes one med I have a bunch
of blank fields, and what if at some point a patient takes more meds than I
alloted for when I build the database.

What is the best way to address this.

Rab

 

[Allen Browne]

You need 3 tables:
- tblPatient (as in your first list)
- tblMedication (one record for each medication, with a MedicationID)
- tblPatientMedication

The 3rd table will have fields like this:
- PatientID relates to tblPatient.PatientID
- MedicationID relates to tblMedication.MedicationID
- StartDateTime Date/time (when patient started this med.)
- EndDateTime Date/Time (when patient finished this course.)
- Dosage how much they are to take.
- DoctorID who ordered/authorized this.

You interface this with a main form bound to tblPatient, and a subform bound
to tblPatientMedication. Show the subform in Continuous Form view. Add as
many rows as the patient needs. You can use a combo box to select the
medication.

Technically, you have a many-to-many relation between patients and
medication (e.g. one patient uses many medications, and one medication is
used by many patients.) The 3rd table (called a junction table), resolves
this into a pair of one-to-many relations. It's the standard teqhnique. For
other examples of using a junction table, see:
http://allenbrowne.com/casu-06.html
http://allenbrowne.com/casu-23.html

Depending on what you are doing, you may need to have 2 junction tables:
- one the records what a patient was supposed to have and how often;
- another that records each dose as it was administered.

 

[Armen Stein]

You need 3 tables:
- tblPatient (as in your first list)
- tblMedication (one record for each medication, with a MedicationID)
- tblPatientMedication

The 3rd table will have fields like this:
- PatientID relates to tblPatient.PatientID
- MedicationID relates to tblMedication.MedicationID
- StartDateTime Date/time (when patient started this med.)
- EndDateTime Date/Time (when patient finished this course.)
- Dosage how much they are to take.
- DoctorID who ordered/authorized this.

You interface this with a main form bound to tblPatient, and a subform bound
to tblPatientMedication. Show the subform in Continuous Form view. Add as
many rows as the patient needs. You can use a combo box to select the
medication.

Technically, you have a many-to-many relation between patients and
medication (e.g. one patient uses many medications, and one medication is
used by many patients.) The 3rd table (called a junction table), resolves
this into a pair of one-to-many relations. It's the standard teqhnique. For
other examples of using a junction table, see:
http://allenbrowne.com/casu-06.html
http://allenbrowne.com/casu-23.html

Depending on what you are doing, you may need to have 2 junction tables:
- one the records what a patient was supposed to have and how often;
- another that records each dose as it was administered.

Allen has given you excellent suggestions for this particular case. If
you want to learn more about designing normalized databases, Database
Design for Mere Mortals by Michael Hernandez is a good place to start.

One of the first clues that your database isn't normalized is numbered
fields, like Medication1, Medication2, etc. Those should always be in
another table as separate rows.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com