Staff count month on month.

[tinynoo]

I want to show how many staff worked for us throughout each month of the year. I think I can only use their start dates to work this out but I don't see how I can calculate to say there were 100 staff in April and 120 in May etc. when it will be the same people month on month give or take new starts, leavers ect. At the moment I have only managed a query that shows how many people have a start month of April, May etc. but I need total staff employed.

Can anyone help?

 

[DrSnuggles]

What is the field and data type for indicating a leaver?
Plus do you know VBA?
And what version of Access are you using?

 

[namliam]

If you make a table that contains all the months you want to report, just a list of numbers, like this:

Number	Type
1	D
1	M
2	M
3	M
4	M
5	M
6	M
7	M
8	M
9	M
10	M
11	M
12	M
2000	Y
2001	Y
2002	Y
2003	Y
2004	Y
2005	Y
2006	Y
2007	Y
2008	Y
2009	Y
2010	Y

Call this table tblNumbers

Now take this query:

SELECT DateSerial([years].[number],[months].[number],[days].[number]) AS FirstOfAllMonths
FROM tblNumbers AS Days, tblNumbers AS Months, tblNumbers AS Years
WHERE (((Days.Type)="D") AND ((Months.Type)="M") AND ((Years.Type)="Y"));

This generates all the possible months from Jan 1 2000 to Dec 1 2010, save it as qryAllMonths

Now take your Staff table and make a new query with it and qryAllMonths:

SELECT qryAllMonths.FirstOfAllMonths, Count(qryAllMonths.FirstOfAllMonths) AS CountOfFirstOfAllMonths
FROM qryAllMonths, tblStaff
WHERE (((tblStaff.StartDate)<=[firstofallmonths]) AND ((tblStaff.EndDate)>=[firstofallmonths]))
GROUP BY qryAllMonths.FirstOfAllMonths;

This is your end result... obviously you need to change out the tblStaff for your table as well as the StartDate and Enddate for your respective columns.

Note: if you have 0 Employees in a certain month... then that month will not be shown....

Good luck, if you have specific questions post them here.

 

[tinynoo]

I don't know visual basic but the idea about making a table for the months etc makes sense. I have a field for the LeaveDate which is a date. I will try and put some of this into action and see how I get on. Thanks.

 

[namliam]

Good luck! Hope you can make it work...

 

[namliam]

Were you able to get it working?