×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

IIF Help on complex query

IIF Help on complex query

IIF Help on complex query

(OP)
Hi everyone,

I am hoping for help on an IIF statement within a query that is not producing results expected. Analyzing a security table where I self-joined table and I need the results to display on one row per user. The scenario is looking at security values where access is granted using security type A and actions are granted with security type C AND D.

The IIF comes into the equation bc not all users have action rights C and D, but they all have A So when there is no C or D rights I need the IIF to just insert a value of 'N/A', but if the rights do exist, the results should return the update rights values in 4 columns. Here is my query:

CODE

SELECT DISTINCT F.USER,, F.PROGRAM, F.SECTYPE, F.RUNAPP, 
IIF(F1.SECTYPE IN ('C', 'D'), (V1.ADD, V1.EDIT, V1.DLETE), 'N/A')
FROM RIGHTS AS F INNER JOIN RIGHTS AS F1 ON (F.USER = F1.USER) AND (F.PROGRAM = F1.PROGRAM) 
ORDER BY 1, 2; 

Expected results would be:
USER // PROGRAM // SEC // RUNAPP // SEC // ADD // EDIT // DLETE //
SHAWN // OIL DRILL // A // YES // C // YES // YES // NO //
JAKE // ACCTS REC // A // YES // N/A // -- // -- // -- //

Results I receive instead is:
USER // PROGRAM // SEC // RUNAPP // SEC // ADD // EDIT // DLETE //
SHAWN // OIL DRILL // A // YES // C // YES // YES // NO //
JAKE // ACCTS REC // A // YES // A // // // //

The second 'A' value is incorrect. Any help is appreciated

RE: IIF Help on complex query

“I self-joined table” - RIGHTS AS F INNER JOIN RIGHTS AS F1
But in your Select you have: V1.ADD, V1.EDIT, V1.DLETE
Where do the V1 come from?

Plus, your outcome from your Select statement will have:
IF(F1.SECTYPE IN ('C', 'D'):
F.USER, F.PROGRAM, F.SECTYPE, F.RUNAPP, V1.ADD, V1.EDIT, V1.DLETE (7 fields)
else
F.USER, F.PROGRAM, F.SECTYPE, F.RUNAPP, N/A (5 fields)

Try:
IIF(F1.SECTYPE IN ('C', 'D'), (V1.ADD, V1.EDIT, V1.DLETE), ('N/A', '--', '--'))

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: IIF Help on complex query

(OP)
The V1 is a typo. It should have been F1 which I corrected, but it still doesn't work.

It doesn't seem to like to have the multi values in the output based on the logical evaluation. For example, if I return just one value such as sectype it will give the correct results, but when I try return sectype, add, edit, etc it throws an error saying the commas are a problem even though I have them all enclosed in parenthesis as noted in my code above.Is it not possible to get multiple values returned in an Inline IF statement?

RE: IIF Help on complex query

(OP)
I've figured out a work around but it is not ideal. It seems it just doesn't like the multiple values pulled into separate columns so what I did was concatenate them all into one field using the '&' and that produced the results accurately. If there is no other solution anyone can help with that I might be missing I can try to use that and do a parsing within excel using a text to column separate (hopefully).

RE: IIF Help on complex query

So you can use three Iif: IIF(F1.SECTYPE IN ('C', 'D'), V1.ADD, 'N/A') AS ADD etc.

combo

RE: IIF Help on complex query

I hate to point the obvious, but what about an additional table with the values you want to display?

ID  RunApp  Add  Edit  Delete
1   C       Yes  Yes   No
2   D       Yes  Yes   No
3   A       Yes  Yes   Yes
4   X       No   No    No
 
And use it instead of IIF in your select statement.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close