SubQuery
in [Queries]
|
From: ram on 1 Apr 2010 14:48 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];
From: ram on 1 Apr 2010 15:04 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" wrote: > 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]; >
From: Daryl S on 1 Apr 2010 15:19 Glad you figured it out! -- Daryl S "ram" wrote: > 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" wrote: > > > 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]; > >
|
Pages: 1 Prev: Subtracting Value from Field if Yes/No = Yes Next: Parameter queries |