Results 1 to 12 of 12
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    Distinct number of Employees by the 12th of the Month in the PreQtr

    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.



    NOTE: in Jan. the previous quarter is 4th quarter of the prior year.

    Current issue: The count function produces the number of employees with a check since the table is 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.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Build a query that returns distinct employees for each month.

    Use that query as source for a query that counts employees by month.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Is a Distinct Count needed here and if so how do I use it with the above?

    Or how do I "build a query that returns distinct employees?
    and it has to be how many by the 12th of each month in each month of the previous qtr

    This simply DISTINCT query is not producing the correct results

    Code:
    SELECT DISTINCT tblCheckHistory.CompanyCode, Count(tblCheckHistory.EmployeeNo) AS CountOfEmployeeNo, Month([CheckDate]) AS [Month], Year([CheckDate]) AS [Year]FROM tblCheckHistory
    GROUP BY tblCheckHistory.CompanyCode, Month([CheckDate]), Year([CheckDate]);
    The count of employees is overstated. Meaning it's not the unique counts, it's all the checks for that employee in that month, etc ...

    CompanyCode CountOfEmployeeNo Month Year Qtr
    C01 11529 3 2020 1
    C01 12306 2 2020 1
    C01 15078 1 2020 1

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Make a totals query to get the first = Min(CheckDate) by EmployeeNo By month Where CheckDate<DateSerial(Year(CheckDate),Month(CheckDa te),12) Then use that in your count query by joining on both EmployeeNo and MinofCheckDate.

    It would help to upload a small sample of your data if you need further help as it would take too long for us to build our own sample.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    DISTINCT query would not do any aggregate calcs.

    Advise not to use reserved words as names for anything.

    SELECT DISTINCT CompanyCode, EmployeeNo, Month([CheckDate]) AS [Mnth], Year([CheckDate]) AS [Yr], DatePart("q", [CheckDate]) AS Qtr FROM tblCheckHistory;

    Now use that query as source to count employees by company/month/year/quarter.

    SELECT CompanyCode, Count(*) AS CountEmp, Mnth, Yr, Qtr FROM Query1 GROUP BY CompanyCode, Mnth, Yr, Qtr;

    Include filter criteria as needed.

    Now I see Gicu's reply. Better deals with the 'by the 12th of each month' requirement.
    Last edited by June7; 04-03-2020 at 11:52 AM. Reason: q
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Thank you both!

    How do I get the DateSerialized 12th of each month to show as a column?
    I can do a cross-tab but the Months are dynamic vs Month1, Month2, Month3 in each quarter.

    Meaning Q1 display 1,2,3 Q2 will display, 4,5,6, etc ... but need it to always shows Month1, Month2, Month3 for each quarter or 1,2,3 every quarter

    Will this work by setting the Colmun Headings in future quarters? It shows it works now but we are in Q1 where the months are actually 1,2,3
    Code:
    TRANSFORM Count(*) AS CountEmp
    SELECT qryCompanyCount.CompanyCode, qryCompanyCount.Year, qryCompanyCount.Qtr
    FROM qryCompanyCount
    GROUP BY qryCompanyCount.CompanyCode, qryCompanyCount.Year, qryCompanyCount.Qtr
    PIVOT qryCompanyCount.Month In (1,2,3);

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try calculating a generic month number and use that as the column headers. Do the calc in preliminary query or in the CROSSTAB.

    TRANSFORM Count(*) AS CountEmp
    SELECT CompanyCode, [Year], Qtr, Choose([Month], 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3) AS MonthNum
    FROM qryCompanyCount
    GROUP BY CompanyCode, [Year], Qtr
    PIVOT "Month" & Choose([Month], 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3) IN ("Month1", "Month2", "Month3");
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Add the field to the query like this:

    12thOfTheMonth:DateSerial(Year(CheckDate),Month(CheckDate),12)

    You could try to create a lookup table to hold the month numbers for each quarter and include that in your query.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    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

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there,

    Here is what I had in mind, let me know if that would work. You can also build a crosstab or use a report instead.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Thank you.
    Was looking for how to set columns using the DateSerial for each month of the quarter to avoid the cross-tab.

    However if June7's cross-tab solutions works for the other quarters and months, that would solve the issue.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, I guess the calc does not need to be in SELECT clause. My mistake.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count Distinct by Month(SQL)
    By DeanMcK in forum Access
    Replies: 2
    Last Post: 02-19-2020, 04:52 AM
  2. Replies: 0
    Last Post: 02-11-2020, 08:12 PM
  3. Replies: 1
    Last Post: 01-31-2014, 11:03 PM
  4. Replies: 1
    Last Post: 08-23-2013, 07:53 PM
  5. Replies: 5
    Last Post: 03-01-2013, 04:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums