Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2015
    Posts
    4

    Unanswered: Multiple iif Statements in 1 Exp

    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. I hope I'm not confusing you. Any help would be greatly appreciated, thanks!

    MAIN DATA TABLE:
    Click image for larger version. 

Name:	Main Data.JPG 
Views:	3 
Size:	27.4 KB 
ID:	16638

    Dept Function EE ID's:
    Click image for larger version. 

Name:	Dept Function EE ID's.JPG 
Views:	4 
Size:	14.4 KB 
ID:	16639

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    table design(s) is/are...
    query design is....
    what are you trying to achieve in plain words with this IIF
    it looks like you are doing a simple lookup, in which case why no just do a left join......
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2015
    Posts
    4

    RE: Multiple iif Statements in 1 Exp

    Hi healdem, thanks for the response. I have done a join (see pic) and it's giving the correct results through level 4 but then it's giving not correct results for level 3, 2Click image for larger version. 

Name:	join.JPG 
Views:	5 
Size:	86.6 KB 
ID:	16640 And the results will fill in a column on the Supervisor look up table
    Last edited by dmd99; 11-19-15 at 13:54.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok so why do you need to use iifs?
    As suggested above use a join for each id

    Mind you it does strike me that your design is iffy. Anywhere you see multipke columns with numeric suffixes its nearly always indicates a non nonrmalised design
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2015
    Posts
    4

    Re:

    My skills are limited so maybe there is no way to do this but since it worked by returning the 4th level supervisor, I thought maybe it would work for the rest. I'm given a spreadsheet with HC Leader 2 included (see attached Pic). What my boss wants is for me to go level by level 6th, 5th, 4th until I reach someone who is not on the Dept Function EE ID's table since they are top level supervisors and not what we need. As you can see in the attached pic, on the 1st row, the HC Leader 2 is Patsiokas. James Meyer & James Cady are top level so I don't want them to populate in to a new column, I do want Patsiokas who is 3rd level. In this case Patsiokas is showing in the HC Leader 2 column but that won't always be the case since people leave.

    Click image for larger version. 

Name:	tst.JPG 
Views:	4 
Size:	53.2 KB 
ID:	16641

    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.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the query you are using is?
    I mean the actual query, the underlying SQL not the query designer.. that gives no indictaion of what the actual join is.

    lets make some assumptions the the absence of hard facts...
    for the following instead of using your horrible, non standard names (you should read up and underwstand why you should NOT be using reserved symbols or words in you tabel and column names.

    your query is, I think going to be something like*...
    Code:
    select my, column, list 
    iif(isnull(eo6.eeid,'supervisor 6 is null',iif(isnull(eo5.eeid,'supervisor 5 is null',iif(isnull(eo4.eeid,'supervisor 4 is null',iif(isnull(eo4.eeid,'supervisor 3 is null',iif(isnull(eo2.eeid,'supervisor 2 is null','value for supervisor1'))))) as derivedvalue
    from (supervisor_lookup as S
     left join EEIDs as E01 on S.directsid = e01.eeid
     left join EEIDs as E02 on S.sup02 = e02.eeid
     left join EEIDs as E02 on S.sup03 = e03.eeid
     left join EEIDs as E02 on S.sup04 = e04.eeid
     left join EEIDs as E02 on S.sup05 = e05.eeid
     left join EEIDs as E02 on S.sup06 = e06.eeid)
    ORDER BY what, ever, sequence, of, columns, you, need
    replace the the table and column names to match your schema. Well actually Id strongly recommend you change your schema (table & column) names to be compliant. At the very least get rid of the # symbol and the spaces. consider redesigning this into a normalised design. had you normalised the design this query would have been a lot easier.....

    replace the 'supervisor x is null' with whatever column or value you want to use IF supervisor x returns null
    the column derivedvalue (as Ive called it the as derived value bit) will contain whatever result you want from this iif

    *..it may well not be, as I tend to forget how Access/JET varies from the standard JOIN syntax
    https://www.google.co.uk/webhp?sourc...ltiple%20joins


    ...could this be done in the query designer... possibly but I tend not to use the query designer, as in the spirit of the GUI world ushered in by Xerox PARC I fifnd the GUI is too gooey, coming between me and the actaul data and in many cases blocking or making what I wanrt to do uneccessarily hard or tricky.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2015
    Posts
    4

    Re:

    I can't get this to work so I'm giving up. Thanks anyway.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •