Agents and sub Agents in a query

I

Inma

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
 
M

Michel Walsh

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top