Reporting on many to many relationships
I have a database of observations made in a classroom. An observation can involve more than one child and can be concerned with more than one curriculum area (CA). I want a report that produces a separate line for each child in each CA. So if an observation involves Child A and Child B working in CA 1 and CA 2 I want the report to have four lines (A1, A2, B1, B2).
I have set it up with a table of Observations linked to a table of Observation Children, each separate record consisting of the observation id and a child id, and to another of Observation CAs, recording the Observation id and a CA id. I thought this method would be preferable to using repeating fields.
When I report on the Observation Children I get one line for each child showing the first CA only. Similarly, if I report on CAs I get one line per incidence of the CA showing the only first child.
It's frustrating because I can construct Find requests to get each combination of Child and CA to be shown but I can't get each instance on the screen at the same time. How do I get this to work?
Thanks in advance for your help