Query help, direct and indirect reports

[Winston2]

I have an Access 2010 DB like the one shown in Fig 1 below and I am using Coldfusion 10 Trial to query this database. At
work I am continually asked for "group" reports which means that the
requestor wants to see everyone in a particular person’s group. For example, if
I get a request to see everyone in David Drew's group then the
finished report would look like Fig 2. below. Can someone please help me with the coldfusion code that might accomplish something like this?

Thanks very much.

Fig 1.

FirstName Lastname EmpID MgrEmpID Email Location
Alex Anderson aaaa noManager Alex.Anderson@nomail.com Philadelphia
Brooke Brown bbbb aaaa Brooke.Brown@nomail.com Philadelphia
Carol Clark cccc aaaa Carol.Clark@nomail.com Philadelphia
David Drew dddd aaaa David.Drew@nomail.com Philadelphia
Erin Eisley eeee bbbb Erin.Eisley@nomail.com Phoenix
Felicia Ford ffff bbbb Felicia.Ford@nomail.com Phoenix
Grace Griffin gggg cccc Grace.Griffin@nomail.com Phoenix
Henry Howard hhhh cccc Henry.Howard@nomail.com Phoenix
Ian Iverson iiii dddd Ian.Iverson@nomail.com New Orleans
Janice Jones jjjj dddd Janice.Jones@nomail.com New Orleans
Karl King kkkk eeee Karl.King@nomail.com New Orleans
Linda Lewis llll eeee Linda.Lewis@nomail.com New Orleans
Mike Miller mmmm ffff Mike.Miller@nomail.com Boston
Nancy Nash nnnn ffff Nancy.Nash@nomail.com Boston
Olivia Ollie oooo gggg Olivia.Ollie@nomail.com Boston
Paula Price pppp gggg Paula.Price@nomail.com Boston
Quentin Quincy qqqq hhhh Quentin.Quincy@nomail.com Cleveland
Robert Richardson rrrr hhhh Robert.Richardson@nomail.com Cleveland
Steve Simmons ssss iiii Steve.Simmons@nomail.com Cleveland
Tina Thomas tttt iiii Tina.Thomas@nomail.com Cleveland
Ursula Urban uuuu jjjj Ursula.Urban@nomail.com Houston
Veronica Valooshka vvvv jjjj Veronica.Valooshka@nomail.com Houston
Walter Watson wwww aaaa Walter.Watson@nomail.com Houston
Xenon Xandruski xxxx wwww Xenon.Xandruski@nomail.com Houston
Yolanda Young yyyy xxxx Yolanda.Young@nomail.com Dallas
Zachary Zubov zzzz xxxx Zachary.Zubov@nomail.com Dallas

Fig 2

FirstName Lastname EmpID MgrEmpID Email Location
Ian Iverson iiii dddd Ian.Iverson@nomail.com New Orleans
Steve Simmons ssss iiii Steve.Simmons@nomail.com Cleveland
Tina Thomas tttt iiii Tina.Thomas@nomail.com Cleveland
Janice Jones jjjj dddd Janice.Jones@nomail.com New Orleans
Ursula Urban uuuu jjjj Ursula.Urban@nomail.com Houston
Veronica Valooshka vvvv jjjj Veronica.Valooshka@nomail.com Houston

[duncancumming]

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.

[Winston2]

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??

Thank you.

[WolfShade]

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.

^_^

[BreakawayPaul]

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 )

ORDER BY...