How to find the first non null value from different columns in Access?

Question

I am trying to build a query where I can get the first non null id available in multiple columns

So my main table is like this,

employee id	name	manager
1	John
2	Doe
3	Jane
4	Phil
5	Jen

I want to lookup the manager from the following table

employee id	Manager ID	Senior Manager ID	Director ID	Senior Director ID
1	21	22	23	24
2		22	23	24
3			23	24
4				24
5		22	23	24

And my output should be something like this

employee id	name	manager
1	John	21
2	Doe	22
3	Jane	23
4	Phil	24
5	Jen	22

So basically, I tried to use this IIF function to do a nested if, and a switch function. Both didn't return the output that I was expecting. Thanks!

Edit 1: I tried with Nz, but it works for ManagerID and SeniorManagerID but doesn't work for DirectorID and SeniorDirectorID for some reason. Meaning, I can get ManagerID and SeniorManagerID, but for the other 2, it returns blank.

Edit 2: I tried to change the query to instead of checking for NULLs, I tried to changed it to empty string, still didn't work.

Answer 1

The Nz() function is used in Access

SELECT Nz(ManagerID, Nz(SeniorManagerID, Nz(DirectorID, Nz(SeniorDirectorID, "None")))) AS Person FROM tablename;

Otherwise, would be a much longer expression using IIf() or Switch and IS NULL. More info http://allenbrowne.com/QueryPerfIssue.html#Nz

Answer 2

Do As following code You can download link from here https://www.mediafire.com/file/3s6l9vjtdi4uzau/TestDB.accdb/file

    Private Sub Command3_Click()
    DoCmd.SetWarnings False
    Dim rs     As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT [employee id], [manager id], [senior manager id], [director id], [senior director id] " & _
        "FROM tblmanagers")
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            Dim MyManager  As Integer
            If Nz(rs![senior director id] & "") <> vbNullString Then
                MyManager = rs![senior director id]
            End If
            If Nz(rs![director id] & "") <> vbNullString Then
                MyManager = rs![director id]
            End If
            If Nz(rs![senior manager id] & "") <> vbNullString Then
                MyManager = rs![senior manager id]
            End If
            If Nz(rs![manager id] & "") <> vbNullString Then
                MyManager = rs![manager id]
            End If
            CurrentDb.Execute "UPDATE tblemployees SET " & _
                              "[employee manager] = " & MyManager & " " & _
                              "WHERE [employee id] = " & rs![employee id] & ""
            rs.MoveNext
        Loop
    Else
    End If
    rs.Close
    Set rs = Nothing
End Sub