Subquery in SQL Statement

[Guest]

Hell

I am using an A2k application. I am using a recordset object based on an sql statement to give me the sum of number of days
an employee is present, no. of overtime hours etc

The SQL statement looks something like this..

stSQL = "SELECT DISTINCTROW tblAttPerm.EmployeeID, Sum((tblAttPerm.Present) WHERE (SELECT tblAttPerm.Present FROM
tblAttPerm WHERE tblAttPerm.IsHoliday = 0)) AS SumPresent, Sum(tblAttPerm.OTHours) AS SumOTHours WHERE EmployeeID = " & !EmployeeID & " GROUP BY tblAttPerm.EmployeeID

I am trying to create a condition for summing tblAttPerm.Present only where tblAttPerm.IsHoliday = 0. I cannot use the "HAVING tblAttPerm.IsHoliday = 0" statement at the end of the SQL because it affects the other values i am summing, which is not what I want.

I just want the tblAttPerm.Present field to be summed up where IsHoliday = 0. The above code Ive written does not work. I get an 'object or variable not defined' error during break execution

Can someone tell me what the correct syntax is for achieving the above

Many thank
Anand

 

[Dirk Goldgar]

Hello

I am using an A2k application. I am using a recordset object based
on an sql statement to give me the sum of number of days
an employee is present, no. of overtime hours etc.

The SQL statement looks something like this...

stSQL = "SELECT DISTINCTROW tblAttPerm.EmployeeID,
Sum((tblAttPerm.Present) WHERE (SELECT tblAttPerm.Present FROM
tblAttPerm WHERE tblAttPerm.IsHoliday = 0)) AS SumPresent,
Sum(tblAttPerm.OTHours) AS SumOTHours WHERE EmployeeID = " &
!EmployeeID & " GROUP BY tblAttPerm.EmployeeID "

I am trying to create a condition for summing tblAttPerm.Present only
where tblAttPerm.IsHoliday = 0. I cannot use the "HAVING
tblAttPerm.IsHoliday = 0" statement at the end of the SQL because it
affects the other values i am summing, which is not what I want.

I just want the tblAttPerm.Present field to be summed up where
IsHoliday = 0. The above code Ive written does not work. I get an
'object or variable not defined' error during break execution.

Can someone tell me what the correct syntax is for achieving the
above.

Many thanks
Anand

I'm not sure, but you may want something like

SELECT
EmployeeID,
Sum(IIF(IsHoliday=0, Present, 0)) As SumPresent,
Sum(OTHours) As SumOTHours
FROM
tblAttPerm
GROUP BY
EmployeeID;