Greetings, fellow SO people.

I'm focusing on a task which has me dealing with an Access database. Here's the setup:

I've three tables:

Tab1 with employee names, ID#s, Manager names and Manager ID#s.
Tab2 with chat info, employee ID#s and employee names.
Tab3 with Manager ID#s, Manager names and team names.

I presently possess a query that chooses the next:

tab2.[employee name], tab2.[employee id], tab3.[chat info], tab1.[manager id], tab1.[manager id], tab3.[team name]
where
tab2.[employee id] = tab1.[employee id] and tab2.[manager id] = tab3.[manager id];

Things I am attempting to accomplish is: I must have the means to put "Unknown" within the "Team" area when the IDs don't complement somewhere across the line. Any ideas?

Thanks ahead of time!

Possibly something similar to:

select tab2.[employee name], 
       tab2.[employee id], 
       tab3.[chat info], 
       tab1.[manager id], 
       Nz(tab3.[team name], 'Unknown') as [team name]
    from (tab2
        left join tab1
            on tab2.[employee id] = tab1.[employee id])
        left join tab3
            on tab2.[manager id] = tab3.[manager id]
SELECT
   tab2.[employee name], tab2.[employee id], 
   tab3.[chat info], tab1.[manager id], 
   tab1.[manager id], 
   Nz(tab3.[team name],"Unknown")
FROM (tab2 
LEFT JOIN tab1 
ON tab2.[employee id] = tab1.[employee id]) 
LEFT JOIN tab3 
ON tab2.[manager id] = tab3.[manager id];

It appears that Access does not support SQL situation claims, so these response is incorrect. I am departing it there for anybody searching for exactly the same trouble with a SQL compliant database.

Apparently use a switch statement to offer the same result this situation shows how a switch can be used. Hopefully that's more useful.

Otherwise, if at all possible, change to a genuine DB :)

Okay, I had been slow from the mark with because you can't use Situation, and Joe's utilization of Nz is most likely your best choice, but there's another Access-specific alternative, presented here just in case they fit your circumstances better:

select tab2.[employee name],
       tab2.[employee id],
       tab3.[chat info],
       tab1.[manager id],
       Iif(IsNull(tab3.[team name]), 'Unknown', tab3.[team name]) as [team name]
    from tab2
        left join tab1
            on tab2.[employee id] = tab1.[employee id]
        left join tab3
            on tab2.[manager id] = tab3.[manager id]

In cases like this, Iif( condition, trueAnswer, falseAnswer ) does exactly the same factor because the Nz, but when your condition is one thing besides an IsNull it may be more flexible.

I am unsure about Access, however in SQL normally you'd left join your tables and use a Situation statement to include the conditional behavior your searching for access might not support this.

The example proven here is fairly standard.

Wow, do access queries really seem like that? Err... Then possibly something similar to this.

tab2.[employee name], tab2.[employee id], tab3.[chat info], tab1.[manager id], tab1.[manager id], case when tab3.[team name] is null then 'Unknown' else tab3.[team name] end as [team name]
where
tab2.[employee id] = tab1.[employee id] and tab2.[manager id] = tab3.[manager id];