Select and sub select

[Mike]

Hi,

I was wondering if I could get some help with the query below. The idea is
to list the incomes generated by every agent per month. The query works
however; one of the agents (Agent_ID = 35) has sub agents. The latter have
assigned an additional ID (sub_ID =35). In my list, I would like the income
generated by the sub agents be added to the sells generated by Agent_ID 35.

I'm sure I need to use a sub query but just cannot get it to work. I
appreciate any help.

TIA

Mike

 

[Guest]

I do not see the problem as the same record that indicates sub_ID =35 also
has Agent_ID = 35. When you total on Agent_ID = 35 you will get all records
for Agent_ID = 35 to in clude those that have a sub_ID =35.

 

[Inma]

Thanks for the reply, Karl.
Would you pls translate that in SQL language because I just cannot get it to
work. I also noticed that the query was not pasted on my prior message so,
here it is...

SELECT SUM(CASE MONTH(DATE_SELL) WHEN 1 THEN COST ELSE 0 END) AS
January, ...
SUM(CASE MONTH(DATE_SELL) WHEN 12 THEN COST ELSE 0
END) AS December, SUM(1) AS Total,
dbo.AGENTS.AGENT_LASTNAME AS AGENT,
dbo.AGENTS.AGENT_ID
FROM dbo.SELLS INNER JOIN
dbo.AGENTS ON dbo.SELLS.AGENT_ID = dbo.AGENTS.AGENT_ID
AND
dbo.SELLS.AGENT_ID = dbo.AGENTS.AGENT_ID
WHERE (YEAR(Y,dbo.SELLS.DATE_SELL) = 2006 AND (dbo.AGENTS.AGENT_ID IN
(7,19, 24, 31, 35))
GROUP BY dbo.AGENTS.AGENT_LASTNAME, dbo.AGENTS.AGENT_ID
ORDER BY SUM(dbo.SELLS.COST) DESC, dbo.AGENTS.AGENT_LASTNAME

AGENT AGENT_ID SUB_ID SELLS IN JAN

TONY 7 35 4000$
MIKE 19 2500$
DON 24 35 3500$
SAL 31 1500$
MARIA 35 1200$

EXPECTED RESULTS

AGENT JANUARY..
MARIA 8700$
MIKE 2500$
SAL 1500$

 

[Guest]

I assumed your data looked like this --
AGENT AGENT_ID SUB_ID SELLS IN JAN
TONY 35 7 4000$
MIKE 19 2500$
DON 35 24 3500$
SAL 31 1500$
MARIA 35 1200$


The way you have your data then open your query in design view and use this
for AGENT_ID --
IIf([SUB_ID] Is Null, [AGENT_ID], [SUB_ID])