Sub Queries

[waqas]

Dear Experts,

i have below query to calculate OFF, Sick Leave, Annual Leave .... etc

these all information in one column against each id and date.

now i want to calculate all in different columns each inforamation in seprate column to do this i am using below query. but it becomes very slow
and when i scroll down it starts calculation again. any other solution to calculation.:cool:

SELECT TblTimeSheetTest.IDNUM, Format([SDay],"mmm") AS Mnth, Sum(TblTimeSheetTest.NHrs) AS SumOfNHrs, Sum(TblTimeSheetTest.OTHrs) AS SumOfOTHrs, Sum(TblTimeSheetTest.PreHrs) AS SumOfPreHrs, Sum(TblTimeSheetTest.TotalHrs) AS SumOfTotalHrs,
(SELECT count(offalias.tanda) from tbltimesheettest as offalias where offalias.tanda = "OFF" and offalias.idnum = tbltimesheettest.idnum) AS OFF,
(SELECT count(offalias.tanda) from tbltimesheettest as offalias where offalias.tanda = "Sick Leave" and offalias.idnum = tbltimesheettest.idnum) AS SL,
(SELECT count(offalias.tanda) from tbltimesheettest as offalias where offalias.tanda = "Annual Leave" and offalias.idnum = tbltimesheettest.idnum) AS AL
FROM TblTimeSheetTest
GROUP BY TblTimeSheetTest.IDNUM, Format([SDay],"mmm");

[waqas]

Dear Experts,

SELECT TblTimeSheetTest.IDNUM, Format([SDay],"mmm") AS Mnth, Sum(TblTimeSheetTest.NHrs) AS SumOfNHrs, Sum(TblTimeSheetTest.OTHrs) AS SumOfOTHrs, Sum(TblTimeSheetTest.PreHrs) AS SumOfPreHrs, Sum(TblTimeSheetTest.TotalHrs) AS SumOfTotalHrs,

(SELECT count(offalias.tanda) from tbltimesheettest as offalias where offalias.tanda = "OFF" and offalias.idnum = tbltimesheettest.idnum) AS OFF,

(SELECT count(offalias.tanda) from tbltimesheettest as offalias where offalias.tanda = "Sick Leave" and offalias.idnum = tbltimesheettest.idnum) AS SL,

(SELECT count(offalias.tanda) from tbltimesheettest as offalias where offalias.tanda = "Annual Leave" and offalias.idnum = tbltimesheettest.idnum) AS AL
FROM TblTimeSheetTest
GROUP BY TblTimeSheetTest.IDNUM, Format([SDay],"mmm");

i am using query to count information which are existing in one column "tanda" which are "OFF", "Sick Leave", "Annual Leave" and more.
i am using above query to get these information in separate separate column for this purpose i am using in query alias which is named as offalias
but it become slow very very slow

how can i retrieve this data in fast

kindly guide me

[mohanvijay]

Try this




SELECT
TblTimeSheetTest.IDNUM,
Format([SDay],"mmm") AS Mnth,
Sum(TblTimeSheetTest.NHrs) AS SumOfNHrs,
Sum(TblTimeSheetTest.OTHrs) AS SumOfOTHrs,
Sum(TblTimeSheetTest.PreHrs) AS SumOfPreHrs,
Sum(TblTimeSheetTest.TotalHrs) AS SumOfTotalHrs,
Sum(iif(TblTimeSheetTest.tanda = "OFF",1,0)) AS OFF,
Sum(iif(TblTimeSheetTest.tanda = "Sick Leave",1,0)) AS SL,
Sum(iif(TblTimeSheetTest.tanda = "Annual Leave",1,0)) AS AL
FROM
TblTimeSheetTest
GROUP BY
TblTimeSheetTest.IDNUM,
Format([SDay],"mmm")

[waqas]

dear,

wonderful very very thanks.:clap: