Subquery

[ram]

Hi To All, Follow-up request from Mar-31-2010

I would like help with the following subquery if possible
Thanks in advance for any help

Table:

tblProduction has the following fields:
[ID], [AgentID], [MonthNumber], [Production]

I would like the query to return the following data
All the agentID, The highest month in the table, sum the production for the
highest month in the table

I’m using the following code

SELECT tblProduction.[AgentID], Sum(tblProduction.[MTDProduction]) AS
[SumOfMTDProduction], Max(tblProduction.[MonthNumber]) AS [MaxOfMonthNumber],
(Select [MTDProduction]
From tblProduction1
Where tblProduction1.[AgentID] = tblProduction.[AgentID]
AND [MonthNumber]=
(Select [MonthNumber]
From tblProduction2
Where tblProduction1.[ID] = tblProduction.[ID] and
tblProduction2.[AgentID] = tblProduction1.[AgentID])) AS TOTALPRODUCTION
FROM tblProduction
GROUP BY tblProduction.[AgentID], tblProduction.[ID];


Thanks for any help

 

[Stefan Hoffmann]

hi,

tblProduction has the following fields:
[ID], [AgentID], [MonthNumber], [Production]

I would like the query to return the following data
All the agentID, The highest month in the table, sum the production for the
highest month in the table

Imho something like this:

SELECT O.[AgentID], O.[MonthNumber], Sum(O.[Production])
FROM [tblProduction] O
HAVING O.[MonthNumber] =
(
SELECT Max(I.[MonthNumber]])
FROM [tblProduction] I
WHERE I.[AgentID] = O.[AgentID]
)
GROUP BY O.[AgentID], O.[MonthNumber];


mfG
--> stefan <--

 

[Daryl S]

Ram -

This should do it:

SELECT tblProduction.[AgentID], Sum(tblProduction.[MTDProduction]) AS
[SumOfMTDProduction], tblProduction.[MonthNumber]
FROM tblProduction
WHERE MonthNumber = (select max(MonthNumber) from tblProduction)
GROUP BY tblProduction.[AgentID], tblProduction.[MonthNumber];

 

[ram]

Thanks this worked great

Ram -

This should do it:

SELECT tblProduction.[AgentID], Sum(tblProduction.[MTDProduction]) AS
[SumOfMTDProduction], tblProduction.[MonthNumber]
FROM tblProduction
WHERE MonthNumber = (select max(MonthNumber) from tblProduction)
GROUP BY tblProduction.[AgentID], tblProduction.[MonthNumber];

--
Daryl S

Hi To All, Follow-up request from Mar-31-2010

I would like help with the following subquery if possible
Thanks in advance for any help

Table:

tblProduction has the following fields:
[ID], [AgentID], [MonthNumber], [Production]

I would like the query to return the following data
All the agentID, The highest month in the table, sum the production for the
highest month in the table

I’m using the following code

SELECT tblProduction.[AgentID], Sum(tblProduction.[MTDProduction]) AS
[SumOfMTDProduction], Max(tblProduction.[MonthNumber]) AS [MaxOfMonthNumber],
(Select [MTDProduction]
From tblProduction1
Where tblProduction1.[AgentID] = tblProduction.[AgentID]
AND [MonthNumber]=
(Select [MonthNumber]
From tblProduction2
Where tblProduction1.[ID] = tblProduction.[ID] and
tblProduction2.[AgentID] = tblProduction1.[AgentID])) AS TOTALPRODUCTION
FROM tblProduction
GROUP BY tblProduction.[AgentID], tblProduction.[ID];


Thanks for any help