Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Distinct Count for Unique Number of Employees in Each Month

Status
Not open for further replies.

supportsvc

Technical User
Jan 24, 2018
249
US
Need help on how to determine the unique number of employees in each month of the quarter by the 12th of the month in the previous quarter.

** How many employees worked by the 12th of each month in the previous quarter.

So tx number of employees each month by the 12th of the month of the previous quarterter, which are Jan-Mar.
NOTE: in Jan. the previous quarter (4th quarter) is in the prior year.

Current issue: The count function produces the number of employees with a check since the table it's from is a check history table.

I have this. However it is counting the employee for every checkdate.
Code:
SELECT tblCheckHistory.CompanyCode, tblCheckHistory.DepartmentNo, tblCheckHistory.EmployeeNo, Count(IIf(Month([CheckDate])=Month(Date())-3,[EmployeeNo],0)) AS Mth1, Count(IIf(Month([CheckDate])=Month(Date())-2,[EmployeeNo],0)) AS Mth2, Count(IIf(Month([CheckDate])=Month(Date())-1,[EmployeeNo],0)) AS Mth3, Count(IIf(DatePart("q",Date())-1,[EmployeeNo],0)) AS Qtr INTO tblCountEmployeesByQtr
FROM tblCheckHistory
GROUP BY tblCheckHistory.CompanyCode, tblCheckHistory.DepartmentNo, tblCheckHistory.EmployeeNo;

Is a Distinct Count needed here and if so how do I use it with the above?
I've seen some posts on Distinct, but can't figure it out here.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top