Sort by Values From a Summary Field

[wwin]

I'd like to sort by values from a summary field.

I have a database for a fundraising event . One table has the donation information (donor's name, amount, student to be credited for donation etc.) that is linked by student ID number to another table of student info. This table may have several donation records per student. The Student table has the student information (name, grade, teacher, etc.). I want a report that is sorted by teacher's name then lists each student in that class by total donation amount per student. The layout works fine if it's sorted by teacher then student name, but I can't figure out how to sort by total donation amount per student.

And as a bonus, no mater how I have it set up I can't seem to get an average donation amount per class (total donations by class/count of students in class).

Any suggestions?

Thanks

[Kevin Frank]

See attached demo. It addresses both your issues -- a) within a class, sorting students by descending order of total donation amount, and calculating the average student donation amount per class.

student_donations.fp7.zip

[Kevin Frank]

Expanded version of the above demo available at

http://tinyurl.com/6zqs23

Contains more counts, averages and totals.

[wwin]

Thanks for your demo. It was much more than I expected, but not quite the set-up I have. I have student information in another table linked to the donation table by student id. I thought I could modify the demo you provided, but there are some calculations I do not understand. I am attaching my modifications if you have the time to look at it.

Thanks

student_donations_2.fp7.zip

[Kevin Frank]

A quirk of GetSummary is that the break field must live in the local table... so I've added two new calculated fields to the "student donations" table: student_name_echo and teacher_name_echo. I also revised the sort criteria and the break fields on the report accordingly.

See attached revised demo.

student_donations_3.zip

Edited November 10, 2008 by Guest
Corrected error in average calculation

[Kevin Frank]

I've just posted corrected versions of both the student_donations_3 demo above, and the expanded version (http://tinyurl.com/6zqs23).