I have a table storing students names, and another table storing enrolled_courses.

In the enrolled_courses table, I have a variable that checks the end date to determine if the student has ended, or still currently enrolled.

Hence each student would have a few enrolled courses, and some already ended, some still currently enrolled.

Now I want to determine if a student is currently enrolled in at least 1 course, or totally not enrolled (means have quitted for good).

From the context of the student table, I would be able to see each student's enrolments.

How can I use this to form a calculation that: if there is at least 1 enrolled course for a student, then the student is enrolled, else the student is ended.

I guess my approach might be wrong, hence would like to find out if anyone has done something like this before. Thanks!

The end date seems (to me) to be a direct attribute of a course, not of an enrolment; so what you (probably) should check if is a student is enrolled in at least one active course: so, with a structure like

Student --< Enrolment >-- Course

you'd have a (n unstored) calculation in Course that determines the active status, like

cIsActive = Case ( Get ( CurrentDate ) <= dateEnd ; 1 )so from the Student context:

Case ( Count ( Course::cIsActive ) ; "Active" )Same logic using SQL (and forgoing the calc field):

ExecuteSQL ( "SELECT COUNT ( * )FROM Enrolment EJOIN Course C ON C.id = E.id_courseWHERE E.id_student = ? AND C.dateEnd >= ?" ; "" ; "" ; Student::id ; Get ( CurrentDate ))