Hello
I foolishly said I would develop a database for work, and I have been trying to teach myself and have become stuck. I am trying to build a database of patients and treatments that they have received in the service in which I work. Each patient can have many treatments, and each treatment can be given to many different patients.
So far I have 3 tables:
tblpatient_details
patientID (PK)
name
address
etc
tbltreatment
treatmentID (PK)
treatment (CBT, Group therapy, etc)
and a junction table
tbltreatment_history
treatment_historyID
patientID (FK)
treatmentID (FK)
I have set up a many to many relationship via the junction table.
All I am trying to do at this stage, is have a form with patient details on it, and a subform where it describes what treatments the patient has had (with the option of editing and adding new treatments).
I have used the form design wizard to make the main form based on tblepatient_treatment, and the subform based on tbltreatment_history.
The subform is set up in datasheet view, and consists of treatment_historyID (PK) and treatmentID (FK). I have turned the treatmentID (FK) into combobox. I want this particular box to display the text descriptions of the treatments from tbltreatments.treatments, but add extra treatments to tbltreatment_history.
At the moment, it allows me to add treatments to tbltreatment_history, but displays them on the subform as the ID numbers from the treatment table, not as the names of the treatments - which is what I want.
Previously, I have tried setting up the subform based on the tbltreatments, and this allows me to have the treatments to be shown in text (e.g. CBT, group therapy, etc). However, if I try to make an addition to the subform, it creates duplicate types of treatments in tbltreatments, rather than make additions changes to tbltreatment_history. (Actually, if I try to make changes, access tells me that the field cannot be updated, but makes changes/additions to the tbltreatments anyway.
I reckon basing the subform on tbltreatment_history is the right way to go, but how to I get it so the user can choose between the text descriptions of treatments stored on tbltreatments?
Please note, that the service may develop more treatments in future, hence I thought it would be a good idea to have a separate table for treatments.
I would be most grateful for any help.
Bruce
I foolishly said I would develop a database for work, and I have been trying to teach myself and have become stuck. I am trying to build a database of patients and treatments that they have received in the service in which I work. Each patient can have many treatments, and each treatment can be given to many different patients.
So far I have 3 tables:
tblpatient_details
patientID (PK)
name
address
etc
tbltreatment
treatmentID (PK)
treatment (CBT, Group therapy, etc)
and a junction table
tbltreatment_history
treatment_historyID
patientID (FK)
treatmentID (FK)
I have set up a many to many relationship via the junction table.
All I am trying to do at this stage, is have a form with patient details on it, and a subform where it describes what treatments the patient has had (with the option of editing and adding new treatments).
I have used the form design wizard to make the main form based on tblepatient_treatment, and the subform based on tbltreatment_history.
The subform is set up in datasheet view, and consists of treatment_historyID (PK) and treatmentID (FK). I have turned the treatmentID (FK) into combobox. I want this particular box to display the text descriptions of the treatments from tbltreatments.treatments, but add extra treatments to tbltreatment_history.
At the moment, it allows me to add treatments to tbltreatment_history, but displays them on the subform as the ID numbers from the treatment table, not as the names of the treatments - which is what I want.
Previously, I have tried setting up the subform based on the tbltreatments, and this allows me to have the treatments to be shown in text (e.g. CBT, group therapy, etc). However, if I try to make an addition to the subform, it creates duplicate types of treatments in tbltreatments, rather than make additions changes to tbltreatment_history. (Actually, if I try to make changes, access tells me that the field cannot be updated, but makes changes/additions to the tbltreatments anyway.
I reckon basing the subform on tbltreatment_history is the right way to go, but how to I get it so the user can choose between the text descriptions of treatments stored on tbltreatments?
Please note, that the service may develop more treatments in future, hence I thought it would be a good idea to have a separate table for treatments.
I would be most grateful for any help.
Bruce
