Skip to main content
W
Winston2
Inspiring
May 24, 2013
Question

Query help, direct and indirect reports

  • May 24, 2013
  • 1 reply
  • 1608 views

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

    This topic has been closed for replies.

    1 reply

    D
    duncancumming
    Participating Frequently
    May 24, 2013

    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.

    W
    Winston2Author
    Inspiring
    May 24, 2013

    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
    WolfShade
    Legend
    May 24, 2013

    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.

    ^_^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices