Multiple iif Statements in 1 Exp

[dmd]

Hi,

I have a table, like below, with the Main data. I also have another table with Dept Function EE ID's (see below) (FYI, I shortened the column headings in my tables below for the sake of space, the iif statement contains the whole heading).

In my query, I brought in the Main Data table & brought in the Dept Function EE ID'stable 4 times and joined each separate ID from the Main data table to the = ID in the Dept Function EE ID's.

I wrote an iif statement: Expr1: IIf([6th Level Supervisor Emp#]=[Dept Function EE ID's]![EE ID],[5th Level Supervisor],IIf([5th Level Supervisor Emp#]=[Dept Function EE ID's_1]![EE ID],[4th Level Supervisor],IIf([4th Level Supervisor Emp#]=[Dept Function EE ID's_2]![EE ID],[3rd Level Supervisor])))

It's working through level 4, so it's picking up 'Tst, Tst4' but it needs to pick up 3rd & 2nd lvl too. Also, the results of the iif will populate a column on the Main Data table. I hope I'm not confusing you. Any help would be greatly appreciated, thanks!

MAIN DATA TABLE:

EE#	EE N	2nd Lvl#	2nd Lvl N	3Rd Lvl#	3Rd Lvl N	4TH Lvl#	4TH Lvl N	5th Level Supervisor Emp#	5TH Lvl N	6th Level Supervisor Emp#	6TH Lvl N
3	EE Tst	95	Tst, Tst2	102845	Tst, Tst3	106695	Tst, Tst4	100263	Tst, Tst5	Some will be blank/null	Some will be blank/nul

Dept Function EE ID's:

Dept Function	EE ID
Tst, Tst5	100263
Tst, Tst4	106695
Tst, Tst3	102845
Tst, Tst2	95

[June7]

Why are trying to save calculated value into table? How do you plan to accomplish that?

Don't understand what you mean by 'needs to pick up 3rd & 2nd lvl too'.

I suspect table is not normalized structure.

[dmd]

The Main data table (see above) has 2nd, 3rd, 4th, 5th & 6th lvl supervisor name and Emp#. The Dept Function EE ID's table (see above) has top level supervisors name & Emp#.

What I'm trying to do is:
- if 6th level Supervisor Emp# (from the Main data table) matches the Emp# on the Dept Function EE ID's table, I need to return the value of 5th lvl from the Main data table,
if 5th level Supervisor EMP# (from the Main data table) matches the Emp# on the Dept Function EE ID's table, I need to return the value of 4th lvl & so on.

Eventually, it won't match and that will be the name I need and will compare that name to a similar column already in the Main data table to find discrepancies. The iif has worked through the 4th level meaning it is returning the 4th level supervisor from the main data table to the new column but that's where the success ends.

[June7]

IIf() can be nested 7 deep. What do you mean by 'success ends' - error messge, wrong resutls, nothing happens?

The expression does not show IIf() for 3rd, 2nd, 1st level supervisors.

Switch() and Choose() functions are often better than IIf().

[dmd]

What I mean by success ends is -

When i use the iif statement : Expr1: IIf([6th Level Supervisor Emp#]=[Dept Function EE ID's]![EE ID],[5th Level Supervisor] (<-this part of the iif gives me the correct results) ,IIf([5th Level Supervisor Emp#]=[Dept Function EE ID's_1]![EE ID],[4th Level Supervisor] (<-this part of the iif gives me the correct results),IIf([4th Level Supervisor Emp#]=[Dept Function EE ID's_2]![EE ID],[3rd Level Supervisor]))) (<-this does not give me the correct results)

so again (& maybe it can't work)What I'm trying to do is:- if 6th level Supervisor Emp# (from the Main data table) matches the Emp# on the Dept Function EE ID's table, I need to return the value of 5th lvl from the Main data table,
if 5th level Supervisor EMP# (from the Main data table) matches the Emp# on the Dept Function EE ID's table, I need to return the value of 4th lvl & so on.

Eventually, I will compare the name of the final result to a similar column already in the Main data table to find discrepancies.

[June7]

Expr1: IIf([6th Level Supervisor Emp#]=[Dept Function EE ID's]![EE ID],[5th Level Supervisor],IIf([5th Level Supervisor Emp#]=[Dept Function EE ID's_1]![EE ID],[4th Level Supervisor],IIf([4th Level Supervisor Emp#]=[Dept Function EE ID's_2]![EE ID],[3rd Level Supervisor], value if not true)))

or maybe:

Expr1: Switch([6th Level Supervisor Emp#]=[Dept Function EE ID's]![EE ID],[5th Level Supervisor], [5th Level Supervisor Emp#]=[Dept Function EE ID's_1]![EE ID],[4th Level Supervisor], [4th Level Supervisor Emp#]=[Dept Function EE ID's_2]![EE ID],[3rd Level Supervisor])

[dmd]

Nothing seems to be working but thank you anyway!

[orange]

Can you post a copy of the database and a clear statement of what you want to accomplish?
Just plain English - no jargon and not how you have done something that isn't working.

[Perceptus]

Maybe you should try using a custom function to handle this, and just pass the function the first set of qualifiers. Nested IIF statements are really just a Kludge to begin with imo.

[June7]

Since the suggested syntax is correct, must be something about the data. As Orange suggests, provide db for analysis.

[dmd]

There are 2 tables;

1. Supervisor Look Up 11-9-15 - which I am given and I import to access. There are several supervisor fields in this table on the same row.
2. Dept Function EE ID’s – this table contains the names and EE ID’s of high level EE’s who would not be considered a supervisor for my purposes.

What I need to do is start at 7^th level supervisor (on the Supervisor Look Up 11-9-15 table), if 7^th level supervisor from this table is on the Dept Function EE ID’s table, I need to go to the 6^th level supervisor, If 6^th level is on the Dept Function EE ID’s table, I need to go to the 5^th level and so on all the way to ‘Direct Supervisor’ but I only need to go as far as direct supervisor if all of the other other supervisors are on the Dept Function EE ID’s table.

I will then compare that result to what's in the column HC Leader 2

[InsuranceGuy]

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!

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

[dmd]

Thanks Jeff, you're awesome, that worked! The only thing, in my sample data, the 1st & 7th record have names in the 6th & 7th level fields. In the end, those are not the names I want so it's working correctly however, their names disappear once I run the query using your code. Is there any way for it to work where their names don't disappear? I've updated the db with your code and attached it here so you can see what I'm saying. If it can't then so be it, it's ok. I just appreciate the work you put in to writing the code to get it to work! Thanks again

[InsuranceGuy]

Happy to try to help -- sorry for the delay. US holiday and all. I have to admit that I am having a fairly difficult time understanding your objective.

What is the output you are seeing? (Please show)

And what do you want to see instead? (Please show)

Thanks!

Jeff

[dmd]

No worries on the delay, I was away too. Hope you had a nice holiday! I've attached a screen shot with an example and some more explanation. Sorry for any confusion or if I'm driving you crazy but again, I really do appreciate what you've done!