1 Reply Latest reply on Dec 29, 2015 9:36 AM by Mike_Mitchell

    Summarize data from a related table


      Probably a simple question from a near-novice:


      I have a simple database for school enrollment. Two tables: Families, and Children.  Related through a unique key, FamIndex.


      I have a "landing page" layout from Families from which users can search for a family name and launch various scripts via buttons.  I would like also to summarize data about Children: # enrolled, # of boys/girls, # of 1st graders etc.


      Thanks for any help.

        • 1. Re: Summarize data from a related table

          Hello, David.


          It depends on how your Children data are recorded, but you can do this via summary fields in the Children table. You will need to create a summary field (Count Of, not Total) for each of the variables you want to track. The question is, what to count?


          If you have a checkbox field like "Enrolled", you can just count that field. If it's empty, they're not enrolled; if it's checked, they are.


          Otherwise, you can add calculation fields to the table that result in a 1 or nothing, depending on the right conditions. For example, if you want to record boys vs. girls, you can have something like this:


               boyCount = Case ( Gender = "Male" ; 1 ; "" )

               girlCount = Case ( Gender = "Female" ; 1 ; "" )


          Then you can do a count on that.