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.

I don't use ms_access, but in usual SQL queries, these things will be done using COALESCE. Something like select coalesce (manager_id, senior_manager_id, director_id, senior_director_id) as manager.... — Jonas Metzler, Oct 13 '22 at 06:07
@JonasMetzler, thats true, but unfortunately MS-Access doesn't have COALESCE function. — Jon Snow, Oct 13 '22 at 06:16
Of course, but 1) I think it might help people who have the same question with another DB and 2) I assumed it could help you finding your query. MS Access should provide a similar function, doesn't it? If it really doesn't cover such simple functionality, I wouldn't use it ;) — Jonas Metzler, Oct 13 '22 at 06:19
Unfortunately, there is no similar function and I am using MS Access as there is a limitation from my organization. — Jon Snow, Oct 13 '22 at 06:20
https://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql — Jonas Metzler, Oct 13 '22 at 06:23
Hi, thanks for this, I tried this, but it works for ManagerID and SeniorManagerID but doesn't work for DirectorID and SeniorDirectorID for some reason — Jon Snow, Oct 13 '22 at 07:19
Then these columns probably contain empty strings, not NULL values. — Andre, Oct 13 '22 at 10:41
@Andre, thanks for that, I tried to change the query to reflect empty strings, but sadly didn't work. — Jon Snow, Oct 13 '22 at 14:16
How could that work - missing a right paren at end of the Nz expression. — June7, Oct 13 '22 at 15:25
If ID fields don't contain null and have empty string then they are not number fields and should be. — June7, Oct 14 '22 at 02:56

June7 · Answer 1 · 2022-10-13T06:31:28.350

2

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

edited Oct 13 '22 at 06:31

answered Oct 13 '22 at 06:21

June7

19,874
8
24
34

Hi, thanks for this. I tried this, but it works for ManagerID and SeniorManagerID but doesn't work for DirectorID and SeniorDirectorID for some reason. I tried to replicate using same logic in excel, it works but for some reason it's not working for SQL. I also tried Switch and Iif, it is also acting the same way. – Jon Snow Oct 13 '22 at 07:18
It works for me. You are going to have to be more specific about what "doesn't work" means. Edit your question with attempt. – June7 Oct 13 '22 at 07:32
I have made the edits in the OP. – Jon Snow Oct 13 '22 at 09:53

A.J · Answer 2 · 2023-08-16T03:39:43.850

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

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

2 Answers2