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 can do an Aggregate Query, grouping on Name. Then create a formula that returns a 1 if role equals "Senior Role", else it returns 0. Then sum this variable, and only return records where the value equals 0 (meaning that Name has no "Senior Role" records.

The SQL code for that would look like this. If you change your query to SQL View, you can cut and paste this code here, then go to Design View to see what it would look like there.

SELECT Table1.Name
FROM Table1
GROUP BY Table1.Name
HAVING (((Sum(IIf([Table1]![Role]="Senior Role",1,0)))=0));

 

[Vexorg]

You can do an Aggregate Query, grouping on Name. Then create a formula that returns a 1 if role equals "Senior Role", else it returns 0. Then sum this variable, and only return records where the value equals 0 (meaning that Name has no "Senior Role" records.

The SQL code for that would look like this. If you change your query to SQL View, you can cut and paste this code here, then go to Design View to see what it would look like there.

SELECT Table1.Name
FROM Table1
GROUP BY Table1.Name
HAVING (((Sum(IIf([Table1]![Role]="Senior Role",1,0)))=0));

Thank you Joe, this is working very well. Is it possible for me to add multiple fields for an exception. So in addition to senior role maybe another variable?

 

[Joe4]

Sure. You should be able to add "OR" situations to the "IIF" statement to accomodate that, i.e.

IIf([Table1]![Role]="Senior Role" Or ([Table1]![Role]="Junior Role",1,0)

 

[Vexorg]

Ah ok, that's what i tried but i repeated 'HAVING' which was causing an error, thank you again!

 

[Vexorg]

Sure. You should be able to add "OR" situations to the "IIF" statement to accomodate that, i.e.

IIf([Table1]![Role]="Senior Role" Or ([Table1]![Role]="Junior Role",1,0)

Hmm this seems to only work some of the time, here is my code (its diff from my provided example):

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

Some of my results still have these fields in them. If i change the 'Or' to 'And' it seems to narrow the results but there are still fields that contain things i dont want. Not sure.

 

[Joe4]

Note in my example, both parts of the OR are contained within a single IIF. You would want to do the same with your multiple conditions.

Or, if you want to do it in multiple IIFs like you did, then you need would use AND.

 

[Vexorg]

Note in my example, both parts of the OR are contained within a single IIF. You would want to do the same with your multiple conditions.

Or, if you want to do it in multiple IIFs like you did, then you need would use AND.

I would like to do it your way using a single IIF because that seems to be the cleanest way to do it but i keep receiving an error:

SELECT REL.tn
FROM REL
GROUP BY REL.tn
HAVING 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)

 

[Joe4]

You forgot the SUM part surrounding the whole IIF.
In my post from yesterday, I was just showing the IIF part to replace, you still need to have the SUM part there.

 

[Vexorg]

Still struggling to get this work. I keep receiving an invalid syntax error (Comma).

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))

Can anyone help me clean it up?