I have a database with a lot of data regarding treatment of cancer patients.
There is a separate table containing patients details.
There are three different tables for surgery, radiotherapy and chemotherapy. They all all joined to the patient data table by LEFT joins so that a patient who exists but has not had surgery can still be identified as not having had surgery etc.
There is a 'dashboard' form where I want the user to be able to select three parameters on all three treatments using combo boxes:
A specific treatment (such as a particular chemotherapy drug)
All treatments (any record where a drug has been given)
No treatment (there is no record in the Chemotherapy table)
For all 3 treatments.
For example I want them to be able to select all patients who had a particular surgery, no radiotherapy and any chemoherapy.
Currently I have managed to do the first 2 using select the combo box value, or leave the combo box blank and use the Is Null function (Fig 1)
I have created a "No Surgery", "No Radiotherapy", "No Chemotherapy" option in the combo box.
Ideally I want a control or expression to say if the combo box = "No Surgery" then select patients (from the patients details table) where there is no surgery ID number.
I have managed all three options for individual treatments but for all three (see Fig 2) but it would involve coding 27 different options to cover all eventualities(I have already done 8) and I suspect that there is a more elegant way of doing it!
I tried running a query off the three individual queries but there were issues with LEFT joins and I don't think that this is the answer.
Please help if you can
Many thanks
Chris
There is a separate table containing patients details.
There are three different tables for surgery, radiotherapy and chemotherapy. They all all joined to the patient data table by LEFT joins so that a patient who exists but has not had surgery can still be identified as not having had surgery etc.
There is a 'dashboard' form where I want the user to be able to select three parameters on all three treatments using combo boxes:
A specific treatment (such as a particular chemotherapy drug)
All treatments (any record where a drug has been given)
No treatment (there is no record in the Chemotherapy table)
For all 3 treatments.
For example I want them to be able to select all patients who had a particular surgery, no radiotherapy and any chemoherapy.
Currently I have managed to do the first 2 using select the combo box value, or leave the combo box blank and use the Is Null function (Fig 1)
I have created a "No Surgery", "No Radiotherapy", "No Chemotherapy" option in the combo box.
Ideally I want a control or expression to say if the combo box = "No Surgery" then select patients (from the patients details table) where there is no surgery ID number.
I have managed all three options for individual treatments but for all three (see Fig 2) but it would involve coding 27 different options to cover all eventualities(I have already done 8) and I suspect that there is a more elegant way of doing it!
I tried running a query off the three individual queries but there were issues with LEFT joins and I don't think that this is the answer.
Please help if you can
Many thanks
Chris
