5 Replies Latest reply on Jul 28, 2012 9:55 PM by steve_ssh

    Sorting to Get the Summary

    jbrown

      In my school database, the principal needs to see the count of kids in each of the 12-16 classes being taught in each of the 6 periods. On a layout based on the enrollment table, I have just the summary fields showing such the counts, but it takes at least 1 minute to sort all 2200 records in order to activate the sub-summary rows and get the count.

      Is there a better, faster way to get the count, without having the principal press sort then go get a cup of coffee while waiting?

       

      Does the Get Summary function help in this aspect?

       

      Show I create a server-side script that sorts the info, then populates it in static fields in the school table, this script to be run each night?

       

      Thanks

      jb

        • 1. Re: Sorting to Get the Summary
          opotoc

          If you use more than one field for your relation calculating the sum, check on the order of the single field relations. The order can make a huge difference.

          Use releations that should result with the smallest records set retuned first. On one of my statistics solution it made a diffence from 30 min to 10 sec!

          Maybe this will help.

           

          A server script is always a good way for caching data, if you only need a daily/hourly updates on your summary.If it takes minute on the client, the server should do it in a few secs.

          • 2. Re: Sorting to Get the Summary
            DavidJondreau

            ExecuteSQL and Virtual Lists! Or maybe just ExSQL and set the fields by script, rather than using Summaries.

             

            Server side is a good choice too.

            • 3. Re: Sorting to Get the Summary
              jbrown

              I'm working on the ExecuteSQL, but am having trouble with the syntax.

              I need to:

              Select all the classIDs from the Enrollment table, get their course name  from the CourseName table, Sort the ClassIDs by period, and count each classID when sorted by period.

               

              Wanna help?

              Here's what i have so far:

              ExecuteSQL (

               

               

              "SELECT MasterSchedule.CourseName, COUNT(HS_Enrollment.pk_ClassID)

              FROM HS_Enrollment INNER JOIN MasterSchedule ON MasterSchedule.ClassID = HS_Enrollment.pk_ClassID

              ";

              "|"; "¶" )

               

              But it aint working.  The count and sorting part is what i have to take care of.

               

              Jeremy Brown

              Teacher / Tech Guy @ KSPA

              • 4. Re: Sorting to Get the Summary
                DavidJondreau

                The things that come to mind are:

                1) Switch the join fields, and put the Enrollment Class ID first, the Schedule ID second.

                2) Check the data types for the join fields and make sure they're the same

                 

                 

                The coolest thing though is from our Belgian friends: You can find out what the actual SQL error is with this elegant function:

                http://www.fmfunctions.com/functions_display_record.php?functionId=335

                • 5. Re: Sorting to Get the Summary
                  steve_ssh

                  David,

                   

                  Thank you for posting this link.  I had no idea this could be done.

                   

                  Best,

                   

                  -steve