Relationship between tables

[kpk]

Experts are requested to help me improve my dbase. I have the following tables.



tblProfile
PtID PK
PtName
Age
Sex
Address
.....

tblDiagnosis
DXID PK
DX

tblDrugs
DrugID PK
DrugName
Strength
Dose
......

I have no problem entering drugs, one by one, for each patient. I want to relate the tables in such a way that I may become able to enter group of drugs ( based on diagnosis) instead of one drug at a time. Of course, I will first enter drugs for each diagnosis. I am not trained in access but I have managed to make a dbase which fulfils my needs. Thanks to all the experts whom I benefited from.
kpk

[June7]

Tables shown do not have relationships.

You will need another table to relate diagnosis with drugs.
tblDiagDrugs
ID (pk)
DiagnosisID (fk)
DrugID (fk)

This doesn't quite make sense to me. How can you assign a dose attribute to a drug? Dosage is so variable according to patient. Not every patient will receive the exact same drug 'package' for same diagnosis.

[kpk]

Many thanks.
You are right about dosage variations. In fact my Form has combos with lookup values which i use for adjusting dose for each patient individually.
I made a join table joining tblProfile, tblDX and tablDrugs. I have a tblProfile Form with tblDrugs as subform but the subform tbldrugs will not store the drugs selected through a combo (tblDX0). In fact i succeed in loading drugs through the combo but the subformDrugs wont move in synchrony with tblProfile Form. I mean the drugs selected through the combo should become a part of the patient's record but I can't figure it out.

[June7]

I was assuming you had a table that related patient with diagnosis, assuming patient can have more than one visit and therefore more than one diagnosis, or even multiple diagnoses for one visit.

tblPatientDiagnosis
PatientID (fk)
Diagnosis (fk)
VisitDate (or VisitID - might need a Visits table)

This will provide the link to the diagnosis/drug relationship. However, I still don't understand how you can 'package' drugs for each diagnosis.