Oh, I do like pain. If I understand your logic correctly as:
1. Eliminate any supervisors in the hierarchy which are listed in [Dept Function EE ID's]
2. Identify the highest ranked supervisor
Then this should work!
Code:
SELECT [Supervisor Look Up - 11-9-15].[Employee No]
,[Supervisor Look Up - 11-9-15].[EE Name]
,[Supervisor Look Up - 11-9-15].[Direct Supervisor Emp#]
,[Supervisor Look Up - 11-9-15].[Direct Supervisor]
,IIf(IsNull([Level2Exemption].[ee id]), [2nd Level Supervisor], NULL) AS [2nd Level Sup]
,IIf(IsNull([Level3Exemption].[ee id]), [3rd Level Supervisor], NULL) AS [3rd Level Sup]
,IIf(IsNull([Level4Exemption].[ee id]), [4th Level Supervisor], NULL) AS [4th Level Sup]
,IIf(IsNull([Level5Exemption].[ee id]), [5th Level Supervisor], NULL) AS [5th Level Sup]
,IIf(IsNull([Level6Exemption].[ee id]), [6th Level Supervisor], NULL) AS [6th Level Sup]
,IIf(IsNull([Level7Exemption].[ee id]), [7th Level Supervisor], NULL) AS [7th Level Sup]
,[Supervisor Look Up - 11-9-15].[HC Leader 2]
,Switch(NOT (IsNull([7th Level Sup])), [7th Level Sup], NOT (IsNull([6th Level Sup])), [6th Level Sup], NOT (IsNull([5th Level Sup])), [5th Level Sup], NOT (IsNull([4th Level Sup])), [4th Level Sup], NOT (IsNull([3rd Level Sup])), [3rd Level Sup], NOT (IsNull([2nd Level Sup])), [2nd Level Sup], True, [Direct Supervisor]) AS [Highest Supervisor]
FROM (
(
(
(
(
[Supervisor Look Up - 11-9-15] LEFT JOIN [Dept Function EE ID's] AS Level7Exemption
ON [Supervisor Look Up - 11-9-15].[7th Level Supervisor Emp#] = Level7Exemption.[EE ID]
) LEFT JOIN [Dept Function EE ID's] AS Level6Exemption
ON [Supervisor Look Up - 11-9-15].[6th Level Supervisor Emp#] = Level6Exemption.[EE ID]
) LEFT JOIN [Dept Function EE ID's] AS Level5Exemption
ON [Supervisor Look Up - 11-9-15].[5th Level Supervisor Emp#] = Level5Exemption.[EE ID]
) LEFT JOIN [Dept Function EE ID's] AS Level4Exemption
ON [Supervisor Look Up - 11-9-15].[4th Level Supervisor Emp#] = Level4Exemption.[EE ID]
) LEFT JOIN [Dept Function EE ID's] AS Level3Exemption
ON [Supervisor Look Up - 11-9-15].[3rd Level Supervisor Emp#] = Level3Exemption.[EE ID]
)
LEFT JOIN [Dept Function EE ID's] AS Level2Exemption
ON [Supervisor Look Up - 11-9-15].[2nd Level Supervisor Emp#] = Level2Exemption.[EE ID];
Cheers,
Jeff