SubQuery

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

ram

HI to All
Daryl S provided me with the following query that works great. however i
wanted to modify the query so that it would add the production for the
highest 2 months.
I tried changing the where statment to the following but it didn't work

WHERE MonthNumber > (select DateAdd("M", -2, max([MonthDate]))) from
tblProduction)

Thanks for any help

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];
 
I used the following code and it seems to work

SELECT tblCSOIssued.EmployeeNumber, Sum(tblCSOIssued.CSOAmount) AS
SumOfCSOAmount
FROM tblCSOIssued
WHERE (((tblCSOIssued.CSODate)>(select (dateadd("m",-2,max([CSODate]))) from
tblCSOIssued)))
GROUP BY tblCSOIssued.EmployeeNumber;

Regards
 
Glad you figured it out!

--
Daryl S


ram said:
I used the following code and it seems to work

SELECT tblCSOIssued.EmployeeNumber, Sum(tblCSOIssued.CSOAmount) AS
SumOfCSOAmount
FROM tblCSOIssued
WHERE (((tblCSOIssued.CSODate)>(select (dateadd("m",-2,max([CSODate]))) from
tblCSOIssued)))
GROUP BY tblCSOIssued.EmployeeNumber;

Regards

ram said:
HI to All
Daryl S provided me with the following query that works great. however i
wanted to modify the query so that it would add the production for the
highest 2 months.
I tried changing the where statment to the following but it didn't work

WHERE MonthNumber > (select DateAdd("M", -2, max([MonthDate]))) from
tblProduction)

Thanks for any help

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];
Click to expand...
Click to expand...
 
Back
Top