Filtering unique records based on a grouping

[AndyDandy]

I am trying to define a query to show only certain records based. Here is my best analogy to describe what I am attempting:


A school where all students must take an english class. They may also take a variety of other classes. I am attempting to filter out only those students who are taking no other classes but english.

I'd like to be able to do this within the user interface so that I can describe the logic and show others if a change is necessary.

Anyone with ideas how best to proceed? I cannot manipulate the tables (read only). There are three tables related in this query. Is a query the best way to achieve this? I use Excel extensively to manipulate table-derived data so this is a process I will likely be doing again.

[CJ_London]

why use an analogy? why not real terms?

yes you will need to use a query and you will need a left join between two of the tables, but without knowing your data structure, can't be any more specific than that.

On the assumption the 3 tables are student, classes and a link table and English is a given but included in the link table do the following

There are a number of ways, but essentially you need to create a first query on the link table using criteria to exclude the English class, you only need to bring through the studentID field

then create another query linking students to the first query on student id. double click on the link to expose properties and change to option 2 (include all students). Then add a criteria for the new query studentID as is null

[isladogs]

Agree with ajax - analogies usually make it harder to give correct advice

Another way of ding this would be to have a query of the English classes for all students & another with a count of classes attended by all students filtered for count>1
Join the 2 queries
If you want those only attending English, modify the filter to count=1

Both methods work - take your pick

[AndyDandy]

In real terms, these are facilities being inspected . Every area of deficiency is a "TAG". There can be multiple inspections for facilities (depending on the severity of the deficiency). The three tables are Facility, Survey, Tags.
Facility1ID SurveyID Tag

[CJ_London]

OK - so either suggestion provided should work