Hello,  I'm new to SQL Server 2005 Express.  Well I've been given this mega
query a co-worker created in Access and they want me to make it work in SQL
Server.  I'm getting a "GROUP BY expression must contain at least one column
that is not an outer reference" error.  Any insight would be appreciated.  

I told you it was a mega query.

  SELECT
      tblEmployee.AsgnCC,
      tblCostCtr.CostCtrName,
      tblEmployee.OTRoster,
      tblOTRoster.OTRosterDescription,
      '' AS TenMileProj,
      '' AS TenMileProjDesc,
     (
         SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn] FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ) AS OTBal,
      tblEmployee.EmpID,
      [LastName] + ', ' + [FirstName] AS Employee,
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '' AS AuxDate,
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].Hrs AS OTHrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      [OneqryOTLast30Days].OTRoster AS OTWrkRstr,
      tblOTRoster.OTRosterType
   FROM  (tblOTRoster
      INNER JOIN  (tblCostCtr
      INNER JOIN  (tblClassification
      INNER JOIN tblEmployee
      ON tblClassification.ClassID = tblEmployee.Class )
      ON tblCostCtr.CostCtr = tblEmployee.AsgnCC )
      ON tblOTRoster.OTRoster = tblEmployee.OTRoster )
      LEFT JOIN OneqryOTLast30Days
      ON tblEmployee.EmpID = [OneqryOTLast30Days].EmpID
   GROUP BY
      tblEmployee.AsgnCC,
      tblCostCtr.CostCtrName,
      tblEmployee.OTRoster,
      tblOTRoster.OTRosterDescription,
      '',
      '',
     (
         SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn] FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ),
      tblEmployee.EmpID,
      [LastName] + ', ' + [FirstName],
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '',
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].Hrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      [OneqryOTLast30Days].OTRoster,
      tblOTRoster.OTRosterType
   HAVING (((tblClassification.ClassBU) <> 22))
    UNION
   SELECT
      tblAuxList.AuxCostCtr,
      tblCostCtr.CostCtrName,
      tblAuxList.AuxOTRoster,
      tblOTRoster.OTRosterDescription,
      '' AS TenMileProj,
      '' AS TenMileProjDesc,
    (

        SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn]  FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        )AS OTBal,
      tblAuxList.EmpID,
      [LastName] + ', ' + [FirstName] AS Employee,
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '' AS AuxDate,
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].Hrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      [OneqryOTLast30Days].OTRoster AS OTWrkRstr,
      tblOTRoster.OTRosterType
   FROM  (tblClassification
      INNER JOIN  (tblEmployee
      INNER JOIN  (tblCostCtr
      INNER JOIN  (tblOTRoster
      INNER JOIN tblAuxList
      ON tblOTRoster.OTRoster = tblAuxList.AuxOTRoster )
      ON tblCostCtr.CostCtr = tblAuxList.AuxCostCtr )
      ON tblEmployee.EmpID = tblAuxList.EmpID )
      ON tblClassification.ClassID = tblEmployee.Class )
      LEFT JOIN OneqryOTLast30Days
      ON (tblAuxList.AuxOTRoster = [OneqryOTLast30Days].[OTRoster]) AND
(tblAuxList.EmpID = [OneqryOTLast30Days].EmpID)
   GROUP BY
      tblAuxList.AuxCostCtr,
      tblCostCtr.CostCtrName,
      tblAuxList.AuxOTRoster,
      tblOTRoster.OTRosterDescription,
      '',
      '',
    (
        SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn]  FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ),
      tblAuxList.EmpID,
      [LastName] + ', ' + [FirstName],
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '',
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].Hrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      [OneqryOTLast30Days].OTRoster,
      tblOTRoster.OTRosterType
    UNION
   SELECT
      tblEmployee.AsgnCC,
      tblCostCtr.CostCtrName,
      tblTenMileRule.MainProject,
      tblOTRoster.OTRosterDescription AS [tblOTRoster_OTRosterDescription],
      tblTenMileRule.Within10Miles,
      tblOTRoster_1.OTRosterDescription AS [tblOTRoster_1_OTRosterDescription]
,
    (
        SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn]  FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ) AS OTBal,
      tblEmployee.EmpID,
      [LastName] + ', ' + [FirstName] AS Employee,
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '' AS AuxDate,
      [OneqryOTLast30Days].OTDateOP,
      Sum([OneqryOTLast30Days].Hrs) AS SumOfHrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      tblOTRoster_1.OTRoster AS OTWrkRstr,
      [OneqryOTLast30Days].OTRosterType
   FROM  ( (tblCostCtr
      INNER JOIN  (tblClassification
      INNER JOIN  (tblOTRoster  AS tblOTRoster_1
      INNER JOIN  ( (tblOTRoster
      INNER JOIN tblTenMileRule
      ON tblOTRoster.OTRoster = tblTenMileRule.MainProject )
      INNER JOIN tblEmployee
      ON tblTenMileRule.Within10Miles = tblEmployee.OTRoster )
      ON tblOTRoster_1.OTRoster = tblTenMileRule.Within10Miles )
      ON tblClassification.ClassID = tblEmployee.Class )
      ON tblCostCtr.CostCtr = tblEmployee.AsgnCC )
      LEFT JOIN OneqryOTLast30Days
      ON tblEmployee.EmpID = [OneqryOTLast30Days].EmpID )
      LEFT JOIN tblEvent
      ON [OneqryOTLast30Days].EventID = tblEvent.OTEventID
   GROUP BY
      tblEmployee.AsgnCC,
      tblCostCtr.CostCtrName,
      tblTenMileRule.MainProject,
      tblOTRoster.OTRosterDescription,
      tblTenMileRule.Within10Miles,
     (
        SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn]  FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ),
      tblEmployee.EmpID,
      [LastName] + ', ' + [FirstName],
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '',
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      tblOTRoster_1.OTRoster,
      [OneqryOTLast30Days].OTRosterType

--
Message posted via http://www.sqlmonster.com