7 Replies Latest reply on Apr 28, 2017 6:16 AM by philmodjunk

    Calculating student progress with many assignments and attempts

    bdenne

      Hello, and thank you for taking the time to read my query!

       

      I am a music teacher and I am trying to bring FileMaker into the teaching of my students in order to differentiate instruction and manage the data that is created.

      --------

      Here is the challenge:

       

      There are many students.

      There are many assignments.

      Students have many attempts at those assignments.

      I need to find the highest score from all the attempts at each assignment and add them together so that the total reflects only the highest score from each assignment.

       

      Example:

      Billy does assignment 1 and gets 3 stars

      Billy does assignment 2 and gets 4 stars

      Billy does assignment 1 again and gets 5 stars

       

      The total should be 9 stars (not 12).

      -------

      How my database is currently structured:

       

      Student table - contains names of students

      Assignment table - contains lists of assignements

      Performance table - acts as a join table for students and assignments.  Each performance record references a single student and a single assignment from the related tables and contains a score for that performance.

       

      Attached is a graphic view of a layout that I created to display this data.  The label is associated with the student table and contains a portal that pulls from the assignment table and the performance table to display each performance attempt.

       

      I would like to keep the record of each performance attempt, but only count the best ones toward each student's progress.

       

      -------

       

      Challenges -

       

      I am trying to get away from the flat file approach that I am using currently because of the number of students (450) and assignments (600) made the large (270,000 cell) spreadsheet unmanageable.  I am hoping to construct a solution that is easily scalable for numbers of students and assignments.

       

      Thank you for any help you can provide!

       

      Sincerely,

       

      Ben Denne

        • 1. Re: Calculating student progress with many assignments and attempts
          beverly

          You have not provided a relationship graph, but I'm going to make these assumptions:

           

          Student -< stu_assign >- Assignments

          (each student can have many assignments and each assignment is attempted by many students, so we have a JOIN table between the two tables).

           

          But you would have "attempts" off the JOIN:

          stu_assign -< Attempts

          (each student with each assignment can have many attempts)

           

          You can have a calculated field in the JOIN file that is MAX( Attempts::total ) for each stu-assign. That would only include the related total fields per student per assignment.

           

          This value can be seen from Students through a portal showing all their Assignments (the stu_assign relationship) and the max_total. You can "tunnel" other related fields in the portal view, including the Assignment name/number.

           

          If you include more information (screenshots), you may get a different answer.

          beverly

          1 of 1 people found this helpful
          • 2. Re: Calculating student progress with many assignments and attempts
            bdenne

            Beverly,

             

            Thank you for your reply.  Here is the screenshot of the relationship graph.

             

             

            I tried to do as you suggested and create a MAX calculation:

            However, that only provided the highest score for the student on that test and did not make that calculation for each test:

            I think I am still missing something.

             

            Thank you!

             

            Ben

            • 3. Re: Calculating student progress with many assignments and attempts
              beverly

              yes!  you are missing something, an entire table of the 'attempts' that should be linked to the Performances table (a join between the "Students" and "Assignments"). The Max() would be about the new table of 'attempts', but be placed in Performances.

              I think you are trying to combine the Performances as attempts. And maybe it is, but there should be the join table between the Students and Assignments and this "performances/attempts" would join to it.

              Think about it a bit.

              beverly

              • 4. Re: Calculating student progress with many assignments and attempts
                bdenne

                Beverly,

                Thank you again for your reply.  It has been 2 weeks and I am still going in circles trying to get this to work.  I had originally set up the Performances table to function as the join table between the Students table and the Assignments table.  When I try create a separate join table, I can't figure out how to connect it to the Performances table, and I can't get it to function in the way it does with the original conception.  Could you (or anyone else) expand on the concepts that you presented.  Again, there seems to be a piece that I am just not understanding yet.

                 

                With great hope and thanks,

                 

                Ben

                • 5. Re: Calculating student progress with many assignments and attempts
                  philmodjunk

                  Can you explain the difference between a "performance" and an "assignment"?

                   

                  Does one record in performance represent one attempt by the student to complete a specific assignment?

                  • 6. Re: Calculating student progress with many assignments and attempts
                    bdenne

                    Thank you for responding. 

                     

                    You are correct.  One record in the performance table represents one attempt by the student to complete the assignment.  This is the thought that drives the join table as performances.  Each student completes many assignments.  Each assignment is completed by many students. 

                     

                    Each performance consists of one student playing one assignment.  So that would constitute the one-to-many relationship.  Each student will have many performances.  Each assignment will have many performances.  But each performance would have just one student and just one assignment. 

                     

                    -Ben

                    • 7. Re: Calculating student progress with many assignments and attempts
                      philmodjunk

                      Your item code fields are not set up correctly.

                       

                      pk_item_code should auto-enter unique values and fk_item_code cannot be an unindexed or global field.