AnsweredAssumed Answered

Summary Field Result without duplicates added

Question asked by DavidTinoco on Apr 6, 2015
Latest reply on Apr 8, 2015 by 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.

Outcomes