Agents and sub Agents in a query

Hi,

I've Orders and Agents tables joint in a query. The Agents table has
AGENT_ID and MNGR_ID. The query pulls the monthly orders per Agent. Some
Agents have sub Agents and I differentiate them with the MNGR_ID. So what I
need to accomplish is to pull the orders of all the Agents and sub Agents
but, the latter, should be grouped as an Agent having MNGR_ID = 35.

I cannot seem to accomplish such task. I'd appreciate some help.

TIA

Inma

Hi,


If you need to regroup together the data about "Constantinople", "Byzance"
and "Istanbul", you can make a table about that:

akas ' table name

This aka ' fields name

Constantinople Istanbul
Byzance Istanbul
Bytown Ottawa
Lutecia Paris

.... 'data sample



Then, the query:

SELECT Nz(aka, givenName) As city, COUNT(*)
FROM myOriginalData LEFT JOIN akas
ON myOriginalData.givenName = akas.this
GROUP BY Nz(aka, givenName) As city


should do the appropriate "count"

In your case, it is not city but employee id, but the principle is the same,
from what I understand.



Hoping it may help,
Vanderghast, Access MVP


"Inma" wrote in message
...
Hi,

I've Orders and Agents tables joint in a query. The Agents table has
AGENT_ID and MNGR_ID. The query pulls the monthly orders per Agent. Some
Agents have sub Agents and I differentiate them with the MNGR_ID. So what
I need to accomplish is to pull the orders of all the Agents and sub
Agents but, the latter, should be grouped as an Agent having MNGR_ID = 35.

I cannot seem to accomplish such task. I'd appreciate some help.

TIA

Inma