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:
patientID(PK) - autonumber
a group treatments table
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)
end date, etc
an individual treatments table
type of treatment (combobox: 1-2-1 CBT, 1-2-1 GET, etc)
a waiting list table
waitinglistID (PK - autonumber)
I also have some junction tables:
date on waiting list
date off waiting list
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
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
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?