2 Replies Latest reply on Jun 2, 2013 7:46 AM by philmodjunk

    Reporting on many to many relationships

    johnswhitehead

      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