Jump to content

Calculating totals by group


ssievert
 Share

This topic is 2270 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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 by ssievert
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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

Screen Shot 2016-04-10 at 8.53.28 PM.png

Screen Shot 2016-04-10 at 8.56.19 PM.png

test.fmp12

Link to comment
Share on other sites

This topic is 2270 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.