Query to find occurences (lack of)

[Vexorg]

Hello Below is my sample table

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Table1</caption> <thead> <tr> <th bgcolor="#c0c0c0">Name</th> <th bgcolor="#c0c0c0">Role</th> </tr> </thead> <tbody> <tr valign="TOP"> <td>BNP PARIBAS INVESTMENT PARTNERS SGR SPA</td> <td>Role</td> </tr> <tr valign="TOP"> <td>BNP PARIBAS INVESTMENT PARTNERS SGR SPA</td> <td>Senior Role</td> </tr> <tr valign="TOP"> <td>CCR ASSET MANAGEMENT
</td> <td>Audit</td> </tr> <tr valign="TOP"> <td>CCR ASSET MANAGEMENT</td> <td>Tax</td> </tr> <tr valign="TOP"> <td>DEXIA ASSET MANAGEMENT
</td> <td>Analyst</td> </tr> <tr valign="TOP"> <td>DEXIA ASSET MANAGEMENT</td> <td>Senior Role</td> </tr> <tr valign="TOP"> <td>EXANE DERIVATIVES</td> <td>Audit</td> </tr> <tr valign="TOP"> <td>EXANE DERIVATIVES</td> <td>Audit</td> </tr> <tr valign="TOP"> <td>FEDERAL FINANCE</td> <td>Analyst</td> </tr> <tr valign="TOP"> <td>FUNDLOGIC</td> <td>Senior Role</td> </tr> <tr valign="TOP"> <td>IKANO CAPITAL</td> <td>Senior Role</td> </tr> <tr valign="TOP"> <td>BARCLAY</td> <td>Tax</td> </tr> </tbody> <tfoot></tfoot> </table>

Hello, each Name should have 1 'Senior Role' i'm trying to find the names where 'Senior Role' never occurs. Each name should have a 'Senior Role'.

The answers should return:
CCR ASSET MANAGEMENT
EXANE DERIVATIVES
FEDERAL FINANCE
BARCLAY

I was thinking something like
Select name
from table1
where role not in 'senior role'

but that's not returning what i need..

 

[Joe4]

You are missing a bracket, and have some parentheses in the wrong places:

SELECT REL.TN
FROM REL
GROUP BY REL.TN
HAVING Sum(IIf(([rel]![Functional Role]="COO") or ([rel]![Functional Role]="Authorized Signatory") or ([rel]![Functional Role]="Sr. Operations Manager") or ([rel]![Functional Role]="CLEAR Validation"),1,0))=0;

 

[Vexorg]

Joe thanks a lot man i really appreciate it!