Hello
Many thanks in advance for any help you are able to give.
I am creating a database for work. I work in service in the NHS. The purpose of the database is to track patients' history in the service, and to be able to see their current status. In the service, patients can have group treatments, individual treatments, and can be on a waiting list for a treatment. patients may have been on many different waiting lists for different treatments, and have had many different group and individual treatments. The service changes, so there needs to be potential to expand - e.g. we might offer a new group treatment at a later stage.
So, I have a patient details table:
tbl_patientdetails
patientID(PK) - autonumber
name
address, etc
a group treatments table
tbl_group
groupID (PK) - number (not an autonumber, as each group has a specific number which I want to be able to select, e.g. group 8 was a CBT group)
type of group (combo box: triple, CBT, etc)
start date
end date, etc
an individual treatments table
tbl_individual
individualID (PK-autonumber)
type of treatment (combobox: 1-2-1 CBT, 1-2-1 GET, etc)
a waiting list table
tbl_waitinglist
waitinglistID (PK - autonumber)
waiting for
I also have some junction tables:
jun_groupevent
groupeventID (PK)
groupIDFK
patientIDFK
jun_individualevent
individualeventID
individualIDFK
patientIDFK
start date
end date
jun_waitinglistevent
waitinglisteventID
waitinglistIDFK
patientIDFK
date on waiting list
date off waiting list
status, etc
I have a main form (frm_patientdetails) on which there are 3 subforms (sf_jun_groupevent, sf_jun_individualevent, sf_jun-waitinglistevent). The subforms are based on queries that join together, for example, tbl_waitinglist with jun_waitinglist). It does this by selecting all the keys on the junction table, and all the keys on the one-side table (e.g. tbl_waitinglist) except the primary key.
I want it so that users can select an event on the subform which then updates the junction table, but not the one-side table (e.g. tbl_waitinglist).
I have set this up on the groupevent subform (sf_jun_groupevent), and now you can select a group from a combobox, and this populates the jun_groupevent table. However, when I tried to set this up on waiting list event subform (sf_jun_waitinglist), everytime I add a new waiting list event on the subform, it also adds it to the one-side table (e.g. tbl_waitinglists: forgive my terminology). so, for example, if i want to put patient A on the waiting list for CBT, I select CBT from the subform, which is fine, but now there is an extra record for CBT in the tbl_waitinglist.
This does not happen on the tbl_group - which probably means it is something to do with the differences between the two tables.
There are two main differences between tbl_group and tbl_waitinglist.
(1) The PK of tbl_group is a number not an autonumber, unlike tbl_waitinglist.
(2) tbl_group has a type of group field which is a combobox based on a value list. tbl_waitinglist has a 'waiting for' field which is a textbox. I did this because, as I said before, the service may add a new treatment, and therefore a new waiting list, at a later stage. It occured to me that it may be useful to have an 'administrator tools' switchboard, where people can go to, for example, a form based on tbl_waitinglist, and add a new record for 'waiting for' field when a new treatment becomes available. Of course, it would also be a good idea to do this for group and individual treatments, but this only occurred to me after I had done group treatments.
Am I missing something fundamental about database design (probably), am I thick (likely), can you help? I am pulling my hair out here. Does anybody know what I am doing wrong?
regards,
Bruce
Many thanks in advance for any help you are able to give.
I am creating a database for work. I work in service in the NHS. The purpose of the database is to track patients' history in the service, and to be able to see their current status. In the service, patients can have group treatments, individual treatments, and can be on a waiting list for a treatment. patients may have been on many different waiting lists for different treatments, and have had many different group and individual treatments. The service changes, so there needs to be potential to expand - e.g. we might offer a new group treatment at a later stage.
So, I have a patient details table:
tbl_patientdetails
patientID(PK) - autonumber
name
address, etc
a group treatments table
tbl_group
groupID (PK) - number (not an autonumber, as each group has a specific number which I want to be able to select, e.g. group 8 was a CBT group)
type of group (combo box: triple, CBT, etc)
start date
end date, etc
an individual treatments table
tbl_individual
individualID (PK-autonumber)
type of treatment (combobox: 1-2-1 CBT, 1-2-1 GET, etc)
a waiting list table
tbl_waitinglist
waitinglistID (PK - autonumber)
waiting for
I also have some junction tables:
jun_groupevent
groupeventID (PK)
groupIDFK
patientIDFK
jun_individualevent
individualeventID
individualIDFK
patientIDFK
start date
end date
jun_waitinglistevent
waitinglisteventID
waitinglistIDFK
patientIDFK
date on waiting list
date off waiting list
status, etc
I have a main form (frm_patientdetails) on which there are 3 subforms (sf_jun_groupevent, sf_jun_individualevent, sf_jun-waitinglistevent). The subforms are based on queries that join together, for example, tbl_waitinglist with jun_waitinglist). It does this by selecting all the keys on the junction table, and all the keys on the one-side table (e.g. tbl_waitinglist) except the primary key.
I want it so that users can select an event on the subform which then updates the junction table, but not the one-side table (e.g. tbl_waitinglist).
I have set this up on the groupevent subform (sf_jun_groupevent), and now you can select a group from a combobox, and this populates the jun_groupevent table. However, when I tried to set this up on waiting list event subform (sf_jun_waitinglist), everytime I add a new waiting list event on the subform, it also adds it to the one-side table (e.g. tbl_waitinglists: forgive my terminology). so, for example, if i want to put patient A on the waiting list for CBT, I select CBT from the subform, which is fine, but now there is an extra record for CBT in the tbl_waitinglist.
This does not happen on the tbl_group - which probably means it is something to do with the differences between the two tables.
There are two main differences between tbl_group and tbl_waitinglist.
(1) The PK of tbl_group is a number not an autonumber, unlike tbl_waitinglist.
(2) tbl_group has a type of group field which is a combobox based on a value list. tbl_waitinglist has a 'waiting for' field which is a textbox. I did this because, as I said before, the service may add a new treatment, and therefore a new waiting list, at a later stage. It occured to me that it may be useful to have an 'administrator tools' switchboard, where people can go to, for example, a form based on tbl_waitinglist, and add a new record for 'waiting for' field when a new treatment becomes available. Of course, it would also be a good idea to do this for group and individual treatments, but this only occurred to me after I had done group treatments.
Am I missing something fundamental about database design (probably), am I thick (likely), can you help? I am pulling my hair out here. Does anybody know what I am doing wrong?
regards,
Bruce