4 Replies Latest reply on Apr 8, 2015 10:03 AM by DavidTinoco

    Summary Field Result without duplicates added

    DavidTinoco

      Title

      Summary Field Result without duplicates added

      Post

      I have 2 tables:

      students

      sessions

       

      Students has a field called dailyQuota and another called weeklyQuota.

       

      For example:

       

      student    quota        weeklyQuota

      1                    5        15

      2                    5        15

      3                      5        15

       

      In the sessions table, I log different session lengths.

       

      So the sessions table might have:

       

      student    session_length    weekEnding        date

      1                           1:15        4/2/15        4/1/15

      2                            1:10        4/2/15        4/2/15

      3                               0:05        4/2/15        3/30/15

      1                               2:05        4/2/15        3/31/15

       

      I need to run a subsummary report on the sessions entries table, grouped by the student id, and date, that shows their daily and weekly quota. The problem is that since there are TWO entries for student 1 in the above example, it doesn’t add up properly (oversums it).

       

      Example if I group the above I get this:

       

      student    sessions

      1                 3:20

      2                 1:10

      3                 0:05

       

      Where it gets screwy is when I add the daily quota field from the students table, as a summary field:

       

      student    sessions    dailyQuota        weeklyQuota

      1                         3:20        10            30        

      2                           1:10        5            15

      3                             0:05        5            15

       

      Since student 1 had 2 entries in the sessions table, it sums TWO dailyquotas instead of just 1 per student.

       

      I am trying to figure out how to show only the student’s daily quota and weekly quota ONCE and sum those once.

        • 1. Re: Summary Field Result without duplicates added
          philmodjunk

          I find that I am puzzled by your example. A summary report would be expected to sum data and I do not see why a value of 3:20 for student 1 produced by summing 1:15 and 2:05 is incorrect as this would indicate that the student's total session time was 3:20.

          What value would be the correct value and why?

          • 2. Re: Summary Field Result without duplicates added
            DavidTinoco

            Phil,

            That is not the problem. It is the quota field.

            I want to get a sum of their quotas, both daily and weekly. For the daily quota, instead of giving me 20 for the quota field above, it gives me 25 because there are 2 instances of student 1 in the sessions table, instead of 20.

            For the weekly, it gives me 60, instead of 45, again because student 1 has 2 entries, to it adds the quota twice, instead of just once per student, even though there may be multiple session entries.

            • 3. Re: Summary Field Result without duplicates added
              philmodjunk

              What is your summary field summarizing? I can see how that would double the quota if the student has two sessions and triple it if they had three. But getting 25 instead of 20 because a student has two sessions doesn't make sense. Won't that student have the same quota specified for each session?

              I would think that for a given student, the quota would be the same for each session and that you would be comparing the sum of all the student's sessions to their specified quota. In such case, you would simply need to include the original quota field from the student table on the layout and not use a summary field.

              • 4. Re: Summary Field Result without duplicates added
                DavidTinoco

                Phil,

                Let me better explain. I am trying to summarize the daily quota per STUDENT not per SESSION record.

                TABLE 1: students
                TABLE 2: sessions

                Table 1 contains:

                student ID
                student Name
                Daily expected Quota

                Table 2:

                session ID
                session length
                date

                When running the report on number of sessions, I want to get (summarized):

                studentName (relatedData)   DailyQuote (related Data)  sessionLength (summarized)

                At the end, there is a trailing grand summary:

                Total Daily Quota, Total Sessions Length

                The daily quota shouldn't be added over and over per each session instance of the same student. It should be summed ONCE per student, no matter the number of sessions that occur. That is what I am trying to achieve.