help with medication database

[EndoDoc]

Maybe someone can help with the structure of my medication database. I have 3 tables: (1) Charts -- with name, address, etc; (2) Visits -- with information specific to each time a patient comes to the office; (3) Medications -- with drug name, dose, etc. "Charts" is the parent table, and "Visits" and "Medications" are both child tables. I set it up this way because I don't want to reenter medications for each new visit. Works great, but I have discovered a problem. I cannot look back to a previous visit and see exactly what medications and doses were listed at that time. I thought about structuring a many-to-many relationship between "Visits" and "Medications", however when I create a new visit, again there are no medications listed. Any ideas? Thanks in advance.

 

[Darrylles]

Hiya,

This is what you have...

Charts (1 ----> many) Visits
Charts (1 ----> many) Medications

You should use:

Charts (1 --> many) Visit (1 --> many) Medication

Not you're restructuring idea of:

Charts (1 --> many) Visit (MANY --> many) Medication

A 'Visit' record should hold 1 date and time only, and a Chart record can have many Visit records (per individual Chart record).
A 'Medication' record should hold 1 drug (or whatever) prescription only, and a Visit can have many Medication records (per individual Visit record).

As for your problem re-entering Medication records - how ARE you entering these?
If you're using a form, it's quite simple to click a button that shows all past Medication prescribed to this Chart (albeit via the Visit records) on a lookup form - clicking the required Medication and sending the Medication details to the main form to create the new Medication record).

Reply to this if you need help.

Regards,

Darrylle





"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[EndoDoc]

Thanks for your response. The way I have it now, I have a main "Chart Form" with two subforms -- one for "Visits" and one for "Medications". It is easy to enter medication data this way. Now your suggestion would also work. However, the medication list often doesn't change much (so in fact one medication can be related to multiple visits; hence my thought for the many-to-many relationship). So using the method you suggest, when a new visit is started I would have to reenter all the medications again. How can I have the new visit record retrieve the medication list from the prior visit, if I construct it as you say?
Hope this makes sense (and hope even more you can help!)
Thanks

 

[Darrylles]

Hiya,

Yep - makes sense, and is exactly as you say, except you see it as manually re-entering, when in fact you would have another form (called from a button on your MEDICATION subform) that displays the LAST known list of prescribed drugs.

This form would populate the 'MEDICATION' subform automatically.

This form is easily adapted to allow you to 'pick & choose' 1, many or all items in the LAST known list to add to the 'MEDICATION' subform.

The [ Charts (1 --> many) Visit (1 --> many) Medication ] relationship is correct from what you say (believe me).

You should think about how data relates before you ever think about manipulating it. A patient has many visits and a visit has (0, 1 or many) prescribed drugs.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."