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

    Summarize data from a related table

    DavidAyer

      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
          Mike_Mitchell

          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.

           

          HTH

           

          Mike