Gicu,
Ok, so how do you count the unique number of employees for each month upto the 12th using the DateSerial as columns in the query so that each month of the quarter through the 12th counts the number of unique employees without having to use a cross-tab?
I tried this but it's not correct.
Code:
SELECT DISTINCT tblCheckHistory.CompanyCode, tblCheckHistory.EmployeeNo, Count(tblCheckHistory.EmployeeNo) AS CountOfEmployeeNo, Year([CheckDate]) AS [Year], DatePart("q",[CheckDate]) AS Qtr, DateSerial(Year([CheckDate]),Month([CheckDate])-2,12) AS Month1, DateSerial(Year([CheckDate]),Month([CheckDate])-1,12) AS Month2, DateSerial(Year([CheckDate]),Month([CheckDate]),12) AS Month3
FROM tblCheckHistory
WHERE (((tblCheckHistory.CheckDate)<DateSerial(Year([CheckDate]),Month([CheckDate]),12)))
GROUP BY tblCheckHistory.CompanyCode, tblCheckHistory.EmployeeNo, Year([CheckDate]), DatePart("q",[CheckDate]), DateSerial(Year([CheckDate]),Month([CheckDate])-2,12), DateSerial(Year([CheckDate]),Month([CheckDate])-1,12), DateSerial(Year([CheckDate]),Month([CheckDate]),12)
HAVING (((DatePart("q",[CheckDate]))=DatePart("q",Date())-1))
ORDER BY tblCheckHistory.CompanyCode, tblCheckHistory.EmployeeNo;
june7
I am getting this with your code
"Your query does not include the specified expression 'Choose([Month], 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3) ' as part of an aggregate function. When I removed this, this is the result
CompanyCode |
Year |
Qtr |
Month1 |
Month2 |
Month3 |
C01 |
2020 |
1 |
66 |
66 |
65 |