MS Access: double loop query with multiple conditions to display on Form

Question

Let's try to phrase this properly, here is a summary of my relevant tables:

1. Table "students" containing student_id, first_name, last_name.
2. Table "courses" containing course_id, title.
3. Table "majors" containing major_id, major_name.

Students are assigned to majors, and courses are assigned to majors (i.e. courses required to obtain the specific major), as a result, I have the two following tables defining which students and courses are assigned to which majors.

4. Table "students_assigned_majors" containing ref_id (PK), student_id (FK), major_id (FK).
5. Table "courses_assigned_majors" containing ref_id (PK), course_id (FK), major_id (FK).

Finally, I have this table called 6. "completed_courses" containing information on which students completed which courses, with columns ref_id (PK), student_id (FK), course_id (FK), completion_date.

I want to create a query that checks which students are not eligible to graduate from the major because they are missing a course required by the major. Any course is not valid if it was completed more than 5 years ago.

I want to display these students and their majors on an existing Form.

I am unsure if this can be done entirely in MS Access SQL... I drafted a VBA procedure on the On_current event of the relevant form, and for now it looks like this: (still Pseudo-code not tested, but here is the idea)

Private Sub Form_Current(major_parameter as String)
    Dim Sql_cour As String
    Dim Sql_stud As String
    
    Dim db As Database
    Dim rs_courses As DAO.Recordset
    Dim rs_students As DAO.Recordset
    
    Set Sql_cour = "SELECT course_id FROM courses_assigned_major WHERE major_id = {major_parameter};"
                
    Set Sql_stud = "SELECT student_id, first_name, last_name FROM students_assigned_majors as SAM  LEFT OUTER JOIN students as S on (SAM.student_id = S.student_id) WHERE SAM.major_id = {major_parameter};"

    Set rs_courses = db.OpenRecordset(Sql_cour)
    Set rs_students = db.OpenRecordset(Sql_stud)
    
    Do While Not rs_students.EOF
        Do While Not rs_courses.EOF
            'check completed_courses table for student_id, course_id, date of course < 5 years ago

            'If conditions not met
                'Add student to new query/table "not_eligible"
                Exit inner Loop
            rs_courses.MoveNext
        Loop
        rs_students.MoveNext
    Loop
End Sub

I am looking for general advice on the best way to accomplish this, and if from your experience, this drafted solution is worth investing time to complete, or if I am completely off track.

More specifically, if this is the way to go, some further uncertainties are:

1. Where do I store this subroutine? Is it OK to store it in the current event of the form?
2. Where do I store the values of the students that do not comply with the requirements? which I later want to display on the form?

Thank you for any help, hope to provide an interesting challenge. Regards

Answer 1

Consider this query to find students that have not completed courses required by associated major:

SELECT stud_maj_courses.student_id, stud_maj_courses.major_id_fk_SAM, stud_maj_courses.course_id_fk_CAM
FROM (
     (SELECT students.student_id, students_assigned_majors.major_id_fk_SAM, courses_assigned_majors.course_id_fk_CAM
      FROM (students INNER JOIN students_assigned_majors ON students.student_id = students_assigned_majors.student_id_fk_SAM) 
      INNER JOIN courses_assigned_majors ON students_assigned_majors.major_id_fk_SAM = courses_assigned_majors.major_id_fk_CAM) 
   AS stud_maj_courses 
INNER JOIN students ON stud_maj_courses.student_id = students.student_id) 
LEFT JOIN completed_courses ON (stud_maj_courses.course_id_fk_CAM = completed_courses.course_id_fk_CC) 
AND (stud_maj_courses.student_id = completed_courses.student_id_fk_CC)
WHERE (((completed_courses.student_id_fk_CC) Is Null)) OR (((completed_courses.completion_date)<#5/31/2016#));