4 Replies Latest reply on Apr 28, 2009 7:42 AM by scottm

    Trouble with Averages



      Trouble with Averages


      I am having some trouble getting my summary averages to work properly.  I have a database to keep track of my grades in a class I teach.  Each project has a record for each student with all section marks totalling to a final mark in that project for each student.  Then I have a summary field that averages these final grades.  When I view the averages on a per project basis everything works fine.


      I have a layout linked to a table which totals all of the project marks up to a final grade for the entire class.  On the layout I see each student as a row (list view) and each column is their project mark, with the final column being the total mark in the class.  When I put fields in to view the average from the related tables, these fields only show the final marks of the last student in the found set rather than the average of the found set.


      I have a feeling that it has something to do with the way Filemaker deals with summaries of summaries, but I'm not sure exactly what it is.



        • 1. Re: Trouble with Averages

          I am a little confused by your structure - what tables and fields do you have?


          I can see you have a Student table (one record per student).

          And you have a Project table (one record per student project).


          So these are related:


          STUDENT ---< PROJECT


          You mention 'section marks' - are these fields in the Project table?

          If so, then you would have a calculation field that adds them together to get the final mark? 

          So then your summary field (still in the Project table?) shows the average of the project totals for a group of project records? 


          Then I get more confused. Please post what fields you have in what table. 

          • 2. Re: Trouble with Averages

            Marks Table:

            Student ID

            Final Grade (Calc - P1 * .4 + P2 * .2 + P2 * .4 )  This gives a weighted final grade from the related tables.

            Average Final Grade (summary)


            Project 1 Table:

            P1StudentID ( related to StudentID above )

            P1Part 1

            P1Part 2

            P1Part 3

            P1Total (Calc - Sum Part 1 - 3)

            P1Average (summary of P1 total)


            Project 2 Table:

            P2StudentID ( same relationship as above )

            P2Part 1

            P2Part 2

            P2Part 3

            P2Total (Calc - same idea as above)

            P2Average (summary)


            Project 3 Table:

            P3StudentID (same)

            P3Part 1

            P3Part 2

            P3Part 3

            P3Total (Calc - same)

            P3Averate (summary)


            My final viewing layout looks kind of like this (attached to marks table).



            StudentID - P1Total - P2Total - P3 Total - Final Grade

            repeat for 57 students


            Trailing Grand Summary:

            Avg P1 (from P1 table) - Avg P2 (from p2 table) - Avg P3 (from p3 table ) - Final Average (from current table)


            Also, the individual tables are much more complex than just 3 parts.  The simplest has 3 and the most complex has about 50, but the basic structure doesn't change.


            I hope this clarifies the structure somewhat.  


            Thank you,


            • 3. Re: Trouble with Averages

              That is much clearer - now I can give you an answer! I have set up a sample file. Not sure that I agree with your data structure but if it works for you then it is OK.


              You need to do your totalling and averaging for each project in the Marks table:



              final grade         Calculation (Number) Unstored,       = P1::total * 0.4 + P2::total * 0.2 + P3::total * 0.4 

              final grade avg   Summary (Number)                       = Average of final grade 


              total p1             Calculation (Number) Unstored,        = P1::mark1 + P1::mark2 + P1::mark3 

              total p2             Calculation (Number) Unstored,        = P2::mark1 + P2::mark2 + P2::mark3 

              total p3             Calculation (Number) Unstored,        = P3::mark1 + P3::mark2 + P3::mark3 


              avg p1              Summary (Number)                        = Average of total p1 

              avg p2              Summary (Number)                        = Average of total p2 

              avg p3              Summary (Number)                        = Average of total p3 

              Then you will be able to get the averages you want. Make sense?
              • 4. Re: Trouble with Averages

                Makes perfect sense.  Thanks for your time in helping me solve the issue.