Creating total summary field from related records

[muskee]

Hi guys, I would really appreciate your help with this one.

So there are 3 tables,
Student --< Enrollment >---- Course

On the enrollment table I have a 2 main fields, field for the final grade and a status field that indicates "Passed / Failed" (depending if the final grade >=3.0) , on the Course table I have a field for Credits. Each course has a specific amount of credits, for example Artificial intelligence has 4 credits. If the student passes the course he accumulates those 4 credits and adds them with all the other credits of all the different courses he has passed.

Student        Course                                           Enrollment 

John Allen    Artificial Intelligence - 4 credits       3.8 - Passed
                    Computer vision   3 credits            2,6-  Failed
                    Robotics    4 credits                       4,4 -  Passed

Number of credits John Allen has: 8 credits (Help making this field)

So, where do I make this summary field that calculates the total of credits but only if the course is passed?
And how do I make it so that it only accumulates the credits for each different person?

Please help

Edited September 6, 2016 by muskee

[comment]

1 hour ago, muskee said:

So, where do I make this summary field that calculates the total of credits but only if the course is passed?

It would be in the Enrollments table - but first you need to have a field you can summarize. You could add another calculation field (also in Enrollments) =

If ( FinalGrade ≥ 3 ; Courses::Credit )

However, this would pose a problem if you ever wanted to change the number of credits of a course, without affecting already existing enrollments. For best results, lookup the credits into a local field in Erollments, and use that field in the calculation above. This will also allow the calculation to be stored, making the whole thing faster.

[muskee]

Thanks comment!
I did as you said, made the credit field a look up from the course table. Made a calculation with the example you gave me (calcCredits) and made a summary field with the total of calcCredits.

The sumary field shows me the total sum of all the credits of all the courses. How do I only show the summary of each student?

[comment]

12 minutes ago, muskee said:

How do I only show the summary of each student?

There are actually two ways to do this:

1. Place the summary field on a layout of Students;

2. Produce a report from the Enrollments table, sorted by student - and place the summary field in a sub-summary by student part.