AnsweredAssumed Answered

Reporting on many to many relationships

Question asked by johnswhitehead on Jun 2, 2013
Latest reply on Jun 2, 2013 by philmodjunk

Title

Reporting on many to many relationships

Post

      

     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

Outcomes