AnsweredAssumed Answered

Reporting on many to many relationships

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


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