What column is indicating to you which group everyone is in? I don't see any commonality between the members of David Drew's group.
All of David Driew's direct reports would have his EmpID as their MgrEmpID. This is repeated for every level in the organization.
In my research to figure this out on my own I understand that a recursive query might be needed??
I've never worked with Access as a database, before. But, if this were SQL (and it might work), then, yes, a LEFT OUTER JOIN would do the trick.
SELECT ta.Lastname, ta.Firstname, ta.EmpID, ta.MgrEmpID, ta.Email, ta.Location
FROM TableA ta LEFT OUTER JOIN TableA tb ON tb.MgrEmpID = ta.EmpID
ORDER BY ta.Lastname, ta.Firstname, tb.Lastname, tb.FIrstname
Or something like that.
LEFT JOINS do in fact work in access. If you have more than one, you need parens.
SELECT tb1.col1, tb2.col2, tb3.col3
FROM ( ( tb1
LEFT JOIN tb2
ON tb1.col1 = tb2.col2 )
LEFT JOIN tb3
ON tb3.col3 = tb1.col1 )