I have a database that keeps patients records and where I can write letters to the patients related consultant. So far there has been an ordinary one-to-many relationship that has worked well. So that when I select a patient record and go through the motion of writing a letter to their consultant, the consultants address and name merge fields are automatically filled out in the letter.
However, very occasionally a patient may need to be related to several consultants. I have created an "other letter" layout. I need to be able to relate a letter to a patient and be able to send the letter to a consultant and have their address details etc automatically filled out.
I am trying to figure out the best way to do this. I have played around with join tables with not much success. The problem seems to be that I lose the first relationship that was made between the patient and consultant. So, when I review previous letter the Consultant details are missing.
Thanks in advance for any advice.