If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count Number Of Employees for Month and Year historically
how i would i get the number of employees for each month and year that we
have data for? my fields are : Representative_Name Approval_Date Termination_Date my data goes back to 06/2007. i can get how many person's were hired/fired in a particular month/year in history but a total number of un-terminated persons up to that month in history is eluding me. hope that makes sense. AC2007/XP. |
#2
|
|||
|
|||
Count Number Of Employees for Month and Year historically
It depends on how you define 'the number of employees for each month'.
Do you measure at the start of the month or the end? What do you want to do with an employee who starts and quits in the same month? SELECT COUNT(*) FROM Employees WHERE (TerminationDate AsOfDate OR TerminationDate IS NULL) AND Approval_Date = AsOfDate; AsOfDate is the date you are calculating from/to. I assume Approval_Date is the hire date. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "tighe" wrote: how i would i get the number of employees for each month and year that we have data for? my fields are : Representative_Name Approval_Date Termination_Date my data goes back to 06/2007. i can get how many person's were hired/fired in a particular month/year in history but a total number of un-terminated persons up to that month in history is eluding me. hope that makes sense. AC2007/XP. |
#3
|
|||
|
|||
Count Number Of Employees for Month and Year historically
dorian,
thank you, but unless i am missing something for each month, i would have to evaluate and requery to get my answer. i was hoping for answers like: month/year:06/2007=5, 07/2007=9...12/2009=14. "Dorian" wrote: It depends on how you define 'the number of employees for each month'. Do you measure at the start of the month or the end? What do you want to do with an employee who starts and quits in the same month? SELECT COUNT(*) FROM Employees WHERE (TerminationDate AsOfDate OR TerminationDate IS NULL) AND Approval_Date = AsOfDate; AsOfDate is the date you are calculating from/to. I assume Approval_Date is the hire date. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "tighe" wrote: how i would i get the number of employees for each month and year that we have data for? my fields are : Representative_Name Approval_Date Termination_Date my data goes back to 06/2007. i can get how many person's were hired/fired in a particular month/year in history but a total number of un-terminated persons up to that month in history is eluding me. hope that makes sense. AC2007/XP. |
#4
|
|||
|
|||
Count Number Of Employees for Month and Year historically
There are a couple ways of doing this, but they involve having an auxiliary
table of some type. One way would be to build a table with a field YearMonth and values in the table of 200701 to 200712 and 200801 to 200812 etc. SELECT C.YearMonth, Count(C.YearMonth) as theCount CalendarTable as C, YourTable as Y WHERE C.YearMonth = Year(Y.Approval_Date) * 100 + Month(Y.Approval_Date) AND C.YearMonth = Year(NZ(Y.TerminationDate,Date())*100 + Month(NZ(Y.TerminationDate,Date()) GROUP BY C.YearMonth You can do this other ways. For instance a table with two fields - the start of the month and the end of the month. OR a table of integers from 0 to 9 that you can use with the DateAdd or DateSerial function to construct the months. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County tighe wrote: dorian, thank you, but unless i am missing something for each month, i would have to evaluate and requery to get my answer. i was hoping for answers like: month/year:06/2007=5, 07/2007=9...12/2009=14. "Dorian" wrote: It depends on how you define 'the number of employees for each month'. Do you measure at the start of the month or the end? What do you want to do with an employee who starts and quits in the same month? SELECT COUNT(*) FROM Employees WHERE (TerminationDate AsOfDate OR TerminationDate IS NULL) AND Approval_Date = AsOfDate; AsOfDate is the date you are calculating from/to. I assume Approval_Date is the hire date. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "tighe" wrote: how i would i get the number of employees for each month and year that we have data for? my fields are : Representative_Name Approval_Date Termination_Date my data goes back to 06/2007. i can get how many person's were hired/fired in a particular month/year in history but a total number of un-terminated persons up to that month in history is eluding me. hope that makes sense. AC2007/XP. |
Thread Tools | |
Display Modes | |
|
|