Subquery

  • Thread starter Thread starter ram
  • Start date Start date
R

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
 
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
Click to expand...
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 <--
 
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];
 
Thanks this worked great

Daryl S said:
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


ram said:
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
Click to expand...
Click to expand...
 
Back
Top