SubQuery

[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];

 

[ram]

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

 

[Daryl S]

Glad you figured it out!

--
Daryl S

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

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];