Access query to remove a group of records

Question

I'm trying to create a query in MS Access that only provides records where, when grouped if one of the criteria is TRUE, then that group is not included in the resultant data set.

I have the following fields ID, Teacher_Name, Dsp_Prd, Course_Key, Long_Description, Sec, Tot_Stds, Contains_CC, SchoolCode, IsCoTeach.

A teacher can appear multiple times in a given period (DSP_prd). However if they are assigned to a class as an assistant (IsCoTeach=TRUE) then all of the classes that they appear in should be filtered from the dataset.

For example:

The results should be:

Thanks for your help!

Answer 1

You can use not exists:

select t.*
from SchoolData as t
where not exists (
    select 1 
    from SchoolData as t1 
    where t1.teacher_name = t.teacher_name and t1.dsp_prd = t.dsp_prd and t1.IsCoTeach = 'TRUE'
)