Q:

Access SQL subquery access field from parent

I have a SQL query that works in Access 2016:
SELECT
    Count(*) AS total_tests,
    Sum(IIf(score>=securing_threshold And score<mastering_threshold,1,0)) AS total_securing,
    Sum(IIf(score>=mastering_threshold,1,0)) AS total_mastering,
    total_securing/Count(*) AS percent_securing,
    total_mastering/Count(*) AS percent_mastering,
    (Count(*)-total_securing-total_mastering)/Count(*) AS percent_below,
    subjects.subject,
    students.year_entered,
    IIf(Month(Date())<9,Year(Date())-students.year_entered+6,Year(Date())-students.year_entered+7) AS current_form,
    groups.group

FROM
    ((subjects
        INNER JOIN tests ON subjects. ID = tests.subject)
            INNER JOIN (students
                INNER JOIN test_results ON students. ID =   test_results.student) ON tests. ID = test_results.test)
    LEFT JOIN
        (SELECT *  FROM group_membership LEFT JOIN groups ON group_membership.group = groups. ID) As g
    ON students. ID = g.student

GROUP BY subjects.subject, students.year_entered, groups.group;

However, I wish to filter out irrelevant groups before joining them to my table.  The table groups has a column subject which is a foreign key. 
When I try changing ON students. ID = g.student to ON students. ID = g.student And subjects. ID = g.subject I get the error 'JOIN expression not supported'. 
Alternatively, when I try adding WHERE subjects. ID = groups.subject to the subquery, it asks me for the parameter value of subjects. ID, although it is a column in the parent query. 
Googling reveals many similar errors but they were all resolved by changing the brackets.  That didn't help. 
Just in case the table relationships help:

Thank you. 
EDIT: Sample database at https://www.dropbox.com/s/yh80oooem6gsni7/student%20tracker.ACCDB?dl=0

A:

I solved it by (a lot of trial and error and) taking the advice here to make queries in the GUI and joining them.  The end result is 4 queries deep!  If the database was bigger, performance would be awful... but now its working I can tweak it eventually. 
Thank you everybody!