Calculating totals by group

[ssievert]

Hello,

I think I have a knot in my brain and could use some help. Working on a simple database for a small school, which contains (amongst others) three tables:

• A students table (PK:StudentID)
• A table that contains a record for each class a student attends (PK: StudentID, ClassID). This table also contains a ProgramID (see below).
• A table that contains a record for each ProgramID along with a human readable description of the program

The classes table contains a numeric field with the hours for that class.

My requirement: On a layout that contains all the student details, I need to show a portal, which contains a list of the  Programs that the student took classes for, which I have covered by a proper relationship. What I am struggling with is that I need to show the total hours for all classes taken by the student by ProgramID. I have lost track how many things I have tried, but I can't seem to figure it out.

I can express it in SQL (my brain retained SQL from 20yrs ago ;)....), if that helps:

SELECT SUM(c.Hours) from Classes c, Students s WHERE c.StudentID = s.StudentID GROUP BY c.ProgramID

I have created a summary field on the Classes table that totals the hours and that works properly, but it totals hours for all classes; not broken down by ProgramID.

Sample Data (Classes table); Fields: StudentID, ClassID, ProgramID, Hours:

S1, C1, P1, 10
S1, C2, P1, 15
S1, C3, P2, 50
S1, C4, P3, 5

The layout for StudentID S1 should show in a portal:

P1: 25 Hours
P2: 50 Hours
P3: 5 Hours

The tables are related like so:

Students.StudentID ------> Classes.StudentID, Classes.ProgramID -----> Programs.ProgramID

I need the Programs relationship to pull in the human description of the program for the portal records (which works).

I must be missing something really simple, but I simply can't see the solution. My portal shows the correct programs, but each program contains the sum of hours for all classes for the student (which kinda makes sense, given how things are laid out right now). I know how to do it on a report using sub-summaries, but am apparently lost here. 

Any help/hints/pointers you could shoot my way are much appreciated. 

Thanks in advance,

Stefan

 

 

Edited April 9, 2016 by ssievert

[comment]

6 hours ago, ssievert said:

On a layout that contains all the student details, I need to show a portal, which contains a list of the  Programs that the student took classes for, which I have covered by a proper relationship. What I am struggling with is that I need to show the total hours for all classes taken by the student by ProgramID.

One way to accomplish this is to add a global gStudentID field to the Programs table, and define a relationship to a new occurrence of the Classes table as:

Programs::ProgramID = Classes 2::ProgramID
AND
Programs::gStudentID = Classes 2::StudentID

Next, add a calculation field to the Programs table = Sum ( Classes 2::Hours ) and place it inside the portal to Programs on the layout of Students.

Finally, add an OnRecordLoad script trigger to the layout to set the Programs::gStudentID field to Students::StudentID.

 

Consider also producing a summary report from the Classes join table instead of a portal.

[ssievert]

Thanks so much for your response. I tried your approach, but I either did something wrong, or it doesn't quite do what I need.

Now the calculated hours are correct for the first program, but the same number appears on the second program. I have the the entries shown in screenshot one in my classes table of my test database, but he portal shows '20' (which is correct for classes of program1) for the classes of Program 2 as well (second screenshot), which should be '15'.

I am attaching my sample database on the off-chance someone wants a little challenge.  But I am getting a feeling I should just put a Button in place of the portal that opens a report with sub-summaries instead. Seems less painful and easier to maintain.

Curiosity still wants me to figure out how to do it with a relationship approach.

Again, thanks for your time and help, much appreciated!!

Stefan

test.fmp12