3 Replies Latest reply on Aug 2, 2013 9:36 AM by philmodjunk

    Averaging Certain records based on criteria

    MikeProcopio

      Title

      Averaging Certain records based on criteria

      Post

           I have a basketball team where we give quizes to our players. I have a table for tests that links to the player profiles. In the test table I have a field for the season as well as the result. I have many tests for each player streched over many seasons. Ho can I set it up where I can take the average for say just the season 2013 and all the other seasons aren't averaged. Thank You 

        • 1. Re: Averaging Certain records based on criteria
          philmodjunk

               Can you provide a more detailed description of the tables you are using. I'd set this up with one record for each persons answer to a single question in a join table that links a table of quizes to a table of players. Then computing an average is a matter of performing a find for all quizzes for the 2013 season.

               In fact, what you describe could be seen as a type of survey. (The only difference between surveys and tests is that tests have right and wrong responses.) This link describes a possible data model for surveys that may serve as a workable data model for your quizzes: Need aid on generating a report from a survey layout.

          • 2. Re: Averaging Certain records based on criteria
            MikeProcopio

                 Phil,

                 I just had a test table that connected to the player's table. I connected the player table with the test table using _pk_playerid<--->_fk_player ID

                 in the test table I have fields for season , test date, and test score. I want to keep the test scores averaged by what season it was taken in. I don't need to track the test itself because each player is given different tests so I think it's the best way just to enter a new record for every test a player takes. When the 2013 season ends I want to keep the averages for them on the 2014 season next year. I was just wondering how to take the a stages just on a specific season so when a player gets into his second year I can keep a stages of the year before and current as well.

            • 3. Re: Averaging Certain records based on criteria
              philmodjunk

                   An average is just another aggregate value. Aggregate values in database parlance refer to values computed from a set of multiple records. Count and Sum are two other examples of Aggregate values.

                   To compute such a value in FileMaker, you have the following options:

                   1) Use the found set approach that I mentioned earlier. On a layout based on Tests, you can perform a find for one player for one season or for all players for one season and a summary field can compute and display the average test score for each player. You can list the individual scores or just have one row for each player and their average. This is usually called a "summary report".

                   2) Define a relationship that matches only to the records for which you want to compute an average:

                   Players::__pkPlayerID = Tests::_fkPlayerID AND
                   Players::Season = Tests::Season

                   WIll only match to the records for one player for one season. You can define a calculation field in Players that uses the Average aggregate function to compute the average score or you can define a summary field in Tests that computes the same average and refer to the summary field from the context of Players. (Be on a layout based on Players)

                   3) Place a summary field from Tests that computes the average inside a one row filtered portal. The relationship you describe, for example matches to all tests taken by a player over all seasons. If you add a portal filter that filters for a specific season you'll get your average score for the season. Note, however, that these averages are "display only". You can't access those averages for other calculations.

                   4) Aggregate values can be computed using the ExecuteSQL function:

                   ExcuteSQL ( "SELECT Average ( a.Score ) FROM Tests a WHERE a.PlayerID = ?" ; "" ; "" ; Players::__pkPlayerID )

                   Warning: I just typed that in here, haven't tested the expression so there may be a syntax "gotcha" that I missed.