[mrwendal]

Hi there,

In Access (2003) I have two tables similar to those listed below, in which the doctor table links to Practice table by PracticeID (i.e. in the example below, Drs Jones, Smith and Walsh are members of the Central Surgery).

Table 1 - Doctor
Fields: DocID, DoctorName, PracticeID
e.g.
1 - Dr Jones - 1
2 - Dr Smith - 1
3 - Dr Walsh - 1
4 - Dr Keegan - 2
5 - Dr Bob - 2

Table 2 - Practice
Fields: PracticeID, PracticeName
e.g.
1 - Central Surgery
2 - West Surgery

I have form which populates a third table, and in this I have a combobox which enables me to select a GP (and enters this in the third table). What I want to happen is for another field on the form to automatically show which Practice the GP belongs to, and for this to be entered in the table also.

I've spent ages on this and know it's a simple thing, but am stuck!
Please can someone point me in the right direction? Many thanks! )

[slurpee55]

Why not just join the two tables in a simple query first, and base the form on that?
Then, once the doctor's name was selected (assuming these are unique or the id's are and that is what you select) then the Practice would show up automatically.

[OBP]

mrwendal, I am a little confused by your question, your table design is correct up to a point, that point being the Doctor can only belong to one Practice, our Doctors belong to one practice but more than one "Practice Address". Your setup won't allow that.
Can you explain what the 3rd table is for. You should not Duplicate data. If the 3rd table is a Linking table with a Many to Many relationship to show what doctors belong to what surguries that is OK, but only the IDs sjould be stored in it.

[mrwendal]

Originally Posted by OBP

mrwendal, I am a little confused by your question, your table design is correct up to a point, that point being the Doctor can only belong to one Practice, our Doctors belong to one practice but more than one "Practice Address". Your setup won't allow that.
Can you explain what the 3rd table is for. You should not Duplicate data. If the 3rd table is a Linking table with a Many to Many relationship to show what doctors belong to what surguries that is OK, but only the IDs sjould be stored in it.

Hi there,
Thanks for your replies.

I was trying to simplify the example, but sounds like it still isn't clear.

I need to record some basic assessment details (in other words, responses to some questions) which I have got working well by using form/subform. This information is entered into tables via a form (patient demographics and assessment results) all linked on patient id.
In the patient demographics table (my "third table") I wanted to record the GP plus the GP practice (for simplicity, assume in this example that a GP only belongs to one practice/address).

I can do the GP fine, but I was trying to get the form to return to screen the linked practice address as well as having this accepted in the underlying patients table.

Following my prior post, I have tinkered some more and I think I am now there!
I have made the GP field a lookup and used a query for the row source which includes the GP and Practice.
In the AfterUpdate event I have something like: GPPrac = cmbGP.Column(3) where GPPrac is the name of the GP Practice field and cmbGP is the GP field.

I have checked the form and selecting a GP now shows the relevant Practice and the form's underlying table contains the correct information too!

Thanks all for your time.