A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count Number Of Employees for Month and Year historically



 
 
Thread Tools Display Modes
  #1  
Old January 27th, 2010, 08:06 PM posted to microsoft.public.access.queries
tighe
external usenet poster
 
Posts: 53
Default 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  
Old January 27th, 2010, 09:10 PM posted to microsoft.public.access.queries
Dorian
external usenet poster
 
Posts: 542
Default 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  
Old January 27th, 2010, 11:01 PM posted to microsoft.public.access.queries
tighe
external usenet poster
 
Posts: 53
Default 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  
Old January 28th, 2010, 12:54 AM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.