Help with Complex (?) report problem

[manwen]

Hope I can explain this clearly:

I have a junction/correlation table that relates patients to doctors. Thus there is one record for every patient /doctor relationship. This is one of a number of tables I use to form a query, which I use to determine whether a patient needs a report/letter sent to them or not. The essence of the report is something like this:

"Dear PATIENT:

Here is a report that has also been sent to DOCTOR1, DOCTOR2, ...etc."


with each DOCTOR in the letter coming from the junction table. However, the way this has turned out so far is that a report will be generated for each relationship. For example, if a PATIENT has 2 doctors, 2 reports will be generated, each with a different DOCTOR name on them; it's easy to see why this is not a desired outcome.

Is there any way I can pull this information from the junction table, "compile it" and stick it in one report; or can anybody think of an easier way to get the results I want?

thanks all!

 

[llkhoutx]

If I understand correctly, Patients to doctors is a one to many relationship. One patient has many doctors. Your junction table has separate rows for each patient doctor relationship.

Generate you letters based on patient in the juntion table, then using a subform list the doctors' name(s) to be incuded in you report as a "can grow/can shrink" subreport textbox, listing them vertically, instead of horizontally because you'll have many different horizontal lengths.

Otherwise, you have to concantenant you letter content with the doctors' names in the middle.