Hello all,
I have a query that I am trying to get a progressive value based on the values in the fields.
I have a query that has Emplid and Level 1, Level 2, Level 3, Level 4, Level 5. The levels are the order of supervisors to Department managers.
Level 1 is the lowest level and level 5 is the highest. the levels have names in the field.
Some of the employees do not have several level managers in level 1, 2 or 3,. for example several employees only have 1 or even 2 managers in Level 4 and 5.
I have it really close in several different ways. I used a switch and IIF statements. But It always stops on Level 2 it seems like. How do i get the query to show the lowest level that does not = Not Applicable?
Below is my sql.
Thank You for any directions
Jim Wagner
SELECT tblReportsToHierarchy.[Level 1], tblReportsToHierarchy.[Level 2], tblReportsToHierarchy.[Level 3], tblReportsToHierarchy.[Level 4], tblReportsToHierarchy.[Level 5], Switch([Level 1]="<Not Applicable>",[Level 2],[Level 2]="<Not Applicable>",[Level 3],[Level 4]="<Not Applicable>",[Level 5],[Level 3]="<Not Applicable>",[Level 4]) AS Melody, IIf([Level 1]="<Not Applicable>",[Level 2],IIf([Level 2]="<Not Applicable>",[Level 3],IIf([Level 3]="<Not Applicable>",[Level 4],IIf([Level 4]="<Not Applicable>",[Level 5],[Level 3])))) AS Jim, IIf([Level 1]="<Not Applicable>",[Level 2],IIf([Level 2]="<Not Applicable>",[Level 3],IIf([Level 3]="<Not Applicable>",[Level 4],[Level 1]))) AS MemoTo
FROM tblReportsToHierarchy;
Posted by: luvmymelody@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar