If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Question
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 |
#2
|
|||
|
|||
Table Question
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 Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rabastan" wrote in message ... 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 |
#3
|
|||
|
|||
Table Question
On Tue, 23 Dec 2008 11:33:45 +0900, "Allen Browne"
wrote: 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 |
Thread Tools | |
Display Modes | |
|
|