Query to show what is NOT there

I have a table and I want to run a query that shows the following:-

I have a Department field and a Subject Group Field.

Each Department has a number of Subject Groups.

So you may have Dept "Teaching", which has 4 Subject Groups, SG T1, SG T2, SG T3 and SG T4

What I want to find out from the data is if there are any instances of mismatch so where there are records that are Dept "Teaching" but there is a Subject Group that is not one of the above listed e.g. SG X1.

How do I write this in the query?

At the moment I have this

DEPT
"Teaching"

SUB GRP
Not "SG T1"
Not "SG T2"
Not "SG T3"
Not "SG T4"

This just returns everything.....is this the wrong syntax??

Thanks!

I thought I need OR........ because there are multiple possibilities ......so in my query design would want

Dept Subject Group
"Teaching" Not In "SG T1"
"Teaching" Not In "SG T2"

etc

Is the Not In the right wording?

I tried Not In "SG T1" but it wouldn't accept it.....

I want the query to show me records where it might have "Teaching" and "SG X1" (i.e. a subject group that is not one of the correct ones, the correct ones being SG T1 etc

I could list all the possible combinations I don't want the Dept and subject groups to be but that would take me a lot longer.......

Let me try to explain more.

I have 6 Departments. Each Department has a different number of Subject Groups. A subject group only belongs to one Department.
In some cases the incorrect Department/Subject Group has been attached resulting in a mismatch.
I need to be able to identify these in my table.
How do I design a query that will show me where there is a mismatch?
So where a record has the Department "Teaching" but the subject group is NOT "SG T1, SG T2, SG T3 or SG T4"?
I could list all the SGs that are NOT T1, T2 etc but that would take me forever and I've to do that for each Department.
If I could just find the syntax in my query design that shows records where Department = Teaching AND Subject Group = NOT "SG T1" etc that would help....unless this is not possible.....

I want the query to show me records where it might have "Teaching" and "SG X1"

I understand that - ever think it can be difficult to get a concept across to someone who is learning? Now try without the benefit of seeing what that person is seeing/working with the same information...

If you are using the Access query designer then start a new query definition, add the table you want to query. Drag the '*' at the top of the field list to the 1st column of the output grid.

Next drag the Department (DEPT?) field to the 2nd column and add 'Teaching' to the Criteria column.

Drag the Subject Group field to the 3rd and add:
NOT IN ("SG T1", "SG T2" ...)
on the same row as the 'Teaching' entry for the DEPT column - This is treated as an implicit AND in Access

Make sure the 'Show' checkboxes for Cols 2 & 3 are unchecked or you will get duplicate columns in the output.

Sorry if I have come across the wrong way I was just trying to be me more clear about what I was trying to do.
I have done everything you have said in your last post but I cannot get it to work.

Copy a representative sample out of the table you want to query and paste to an excel sheet, Anonymise any personal data, if any, and upload to the forum. (Only suggesting Excel as it's easier/smaller and there's bound to be shed load of other unrelated stuff in the DB).

I'll build the query and post the SQL text you can just paste into a new query.

Thank you! :-)

Sorry just needed to create a table showing you the correct Dept and Sub Group relationships

These will not change....

Ta for that. Other things have caught up with me. If no one else comes into this I'll get back to it tomorrow morning

Great, thank you so much for your help! :-)
If I mange to get my "Access Amateur" brain around it in the meantime I will post my solution!

Hi,

Thank you for this. Unfortunately being very new to SQL I don't really understand how to write all the code and create the necessary tables with inner joins.

Is there any way to build the query in the wizard rather than using SQL?

Thanks

It is still needed, I am just struggling to understand the SQL side as my knowledge is very limited. I had to find an alternative solution as I was under pressure to get this done but I want to know how to do it right.

I have posted a separate question about something else I need to do in my database.
Thank you for clarifying.