Help with Query

[snoopy2003]

Hello,

I have a table Employees:
ID
Manager1
Manager2
Manager3
EmployeeType (A,B)

Manager1, Manager2, Manager3 are ID numbers of employees.

I wish to retrieve for each employee how many subordinate he has by EmployeeType .



I mean: for each ID:
count how many records has Manager1=ID or Manager2=ID or Manager3=ID and their EmployeeType = A
count how many records has Manager1=ID or Manager2=ID or Manager3=ID and their EmployeeType = B

My desired output for example:
ID #A #B
1 10 5
2 4 5

Can you help me ?

Thank you for your help

[June7]

This is one way:
TRANSFORM Count(Query1.[ID]) AS CountOfID
SELECT Query1.[MgrID]
FROM (SELECT ID, Nz([Manager1],Nz([Manager2],[Manager3])) AS MgrID, EmployeeType FROM Employees) As Query1
GROUP BY Query1.[MgrID]
PIVOT Query1.[EmployeeType];

[snoopy2003]

Thank you for your help.
I will try to implement it according to your suggestion.

Thank you for your support

[snoopy2003]

Hello,

I did not success in getting the desired result with the above query.

Is there any other way to implement it ?

Thank you for your help

[snoopy2003]

I tried to solve my problem with multiple queries
but my backend is located on a server and the response time is not good.

I understand that my goal can be achieved by cross tab but I dont know how to do it.

My input:
ID
Manager1
Manager2
EmployeeType

for example:
ID=1 Manager1=Null Manager2=Null EmployeeType=A
ID=2 Manager1=1 Manager2=Null EmployeeType=B
ID=3 Manager1=2 Manager2=Null EmployeeType=B
ID=4 Manager1=1 Manager2=2 EmployeeType=A

Output:
ID
#Employees Type A
#Employees Type B

output:
ID=1 #EmployeesTypeA=1 #EmployeesTypeB=1
ID=2 #EmployeesTypeA=1 #EmployeesTypeB=1


Thank you for your help

[rpeare]

I made an example of your table and called it SNOOPY2003, so you will have to change references in the query to your table name where appropriate.

Make a query with this code:

SELECT Snoopy2003.Manager1 AS Manager, Snoopy2003.EmployeeType
FROM Snoopy2003
WHERE (not isnull(manager1))
UNION SELECT Snoopy2003.Manager2 AS Manager, Snoopy2003.EmployeeType
FROM Snoopy2003
WHERE(not isnull(Manager2))

Call this query Qry_CrosstabSetup

Make a query with this code:

TRANSFORM Count(Qry_CrosstabSetup.Manager) AS CountOfManager
SELECT Qry_CrosstabSetup.Manager
FROM Qry_CrosstabSetup
GROUP BY Qry_CrosstabSetup.Manager
PIVOT Qry_CrosstabSetup.EmployeeType

it should give you the results you seem to want.

[snoopy2003]

Hello,

Thank you very much. I appricaiate your help.
This is what I was trying to implement.

I had to add the keyword 'All' to the 'UNION' command
in order to retrieve all the results from both queries.

Your query will be very helpful for me and makes the
calculation process simpler and faster.

Thank you