Key Still In Table

[CJBIRKIN]

Hi

I have a form/subform setup with cascading comboboxes on the subform.

I have three comboboxes.
The first lists a number of hospitals.
The second displays a list of wards which are filtered using the hospital selected in the first combobox.
The third is a list of consultants who also belong to the hospital selected.

The three lists are derived from fields selected from three seperate tables. Tbl_Hospitals , Tbl_wards , Tbl_medical staff.

I am storing all of the selected primary keys in another table Tbl_hospital_episode i.e if hospital x is selected then its primary key is entered into Tbl_hospital_episode, if ward y is selected then its primary key is entered into Tbl_hospital_episode etc.

I have noticed however that if hospital --> ward -->consultant are select and later the hospital is changed ,for that record, to one where there are no current wards or consultants associated with that hospital, the keys for the consultant and ward are left in Tbl_hospital_episode even though the cascaded comboxes are empty.

Can anyone explain this?

Thanks

Chris

 

[Rich]

Have you checked Cascade Update Related fields?

 

[CJBIRKIN]

Hi

I guessed i was going to have problems with this from the start.

Basically i am trying to develop a db to record data about patients transfered between hospitals. There is an existing paper form that i cannot alter from which the db gets the data.

I analysed the form and it appeared that there where four main tables. patient, refering hospital episode, transfer episode, and recieving hospital episode.

The data is entered by a third party Network on behalf of the hospitals involved. There are roughly 5 hospitals in each of 3 networks and each hospital may transfer patients to 1 of a dozen or more hospitals many of which may be outside that network.

This creates a problem as there could be a number of wards in each hospital with the same name and there are often consultants and nurses with the same name in the same hospital let alone the dozens here.

It therefore becomes impossible to store just the staff member or the ward in any given table you need to use a compound key of the hospital and the ward or staff member and force the user to make each name unique.

There is no way that any one user will know all the staff involved so codes are out of the question, they are completley reliant on the data from the form.

Basically I am unsure how to relate the tables. And therefore i don't know which relationships would benefit from
Cascade Update Related fields? see pic

To add insult to injury the receiving hospital episode has the same problem..

Any ideas greatfully received

Chris

 

[CJBIRKIN]

sorry pic here

 

[CJBIRKIN]

Hi

Thanks Pat , I came to the same conclusion last night (3am!! ).
However i am still unsure as to the data structure that i have designed and i am loathe to continue until i know that i've got it right. Do you have any advice??

Thanks again

Chris

 

[CJBIRKIN]

Hi

Thanks for the advice Pat, I will create the compound key as you suggest. I have coded the removal of the foreign key data from the refering hospital table if the user decides to alter the data post entry and it works fine. I just hope i don't get to the end of this and realise i've made some stupid mistake in my analysis, then again why change the habit of a lifetime


Cheers

Chris