Hi,

I'm trying make a query that looks at a single table and looks to see if a student is a team called CMHT and in a medic team - if they are I don't want to see the result.

I only want see if there only in CMHT or medic not both.

Would the right direction be using sub query to filer it out? I've done a search on NOT IN but how could you get to see check if its in more then 2 teams are not?

 

Student     Team          ref
1           CMHT           1
1           Medic          2
2           Medic          3 this would be in the result
3           CMHT           5 this would be in the result

So far I've done the following code would I need use a sub query or do a self join and filter it that way?

    SELECT Table1.Student, Table1.Team, Table1.refnumber

FROM Table1
WHERE (((Table1.Team) In ('Medics','CMHT'))