I'm trying to create a report, but i'm having trouble figuring out how to get what i want.
My main table has a record for each student, a related table has funding information, one record for each student for each academic year.
In my main table I have 4 supervisor fields (Super #1; Super #2; Super #3 and Super #4), students can have up to 4 supervisors. These can change year to year.
In my funding table, i've created 4 fields (Super 1 of 4; Super 2 of 4; Super 3 of 4 and Super 4 of 4) For each academic year, i've chosen anywhere from 1-4 supervisors and indicated how much that supervisor has contributed to each semester (Fall; Winter: Summer) i've created a calculation field that adds up the Fall Winter and Summer funding for each of the 4 Super's.
I want a report that shows all of the supervisors (all 8 supervisor fields from both tables are drop downs from a value list) and show how many students each supervisor has been assigned to (just a number not a list of students as supervisors have the same students as other supervisors).
then i want the amount of money each supervisor has paid out for all of his students for that academic year. so it will have to pull the calculation field of how much he/she spent on each of the students assigned to him and add them all up on this report.
So the report should look like this:
Supervisor's Name # Stu's $15,793.22
I'm not sure how to get the report to show one line for each of the supervisors from my value list, then calculate the # of student's that supervisor has been attached to (using Supervisor#1; Supervisor#2; Supervisor #3 or Supervisor#4) then get it to pull the amount that particular supervisor spent on each student (listed as Super 1of 4; Super 2 of 4; Super 3 of 4 or Super 4 of 4) and then add each amount up.
I'm attaching two images, the first is my main database showing the student and the 3 supervisors; the second is the funding layout showing how much each of the 3 paid towards this one student.