Turnover Calculation

[syedadnan]

Regards,
I am looking automation through VBA for the calculation of HR employees turnover on monthly basis, i have a query showing fields ..

employee name
joining date
department
section
employee left (Yes/No)
Left date

now i am looking for;

i) Opening strength: Total employees strength through joining date with criteria of department and section wise at month start

ii) Closing Strength: Total employees strength through joining date with criteria of department and section wise at month end (subtracting the employees left through employee left field y/n)

iii)Left Employees: number of employees left at the end of each month with the criteria of department and section wise

iv) Turnover: The number of left employees will be divided by the dividend of (sum of opening & closing employees divided by 2)


I know its in detailed but a help would be highly appreciated ...

 

[plog]

This sounds like it can be solved purely by SQL and not VBA. For example, iii would be this:

SELECT Month([Left date]) AS MonthLeft, Year([Left date]) AS YearLeft, COUNT([Left Date])
FROM YourQueryNameHere
WHERE IsNull([Left date])=False
GROUP BY Month([Left date]), Year([Left date])

iv would require probably a sub-query or two. And you've not provided a clear description of what "strength" is so, I would need more information on that for i and ii.

Before proceeding however, I advise you rename your field names to eliminate spaces. Having them just makes coding a little harder. Use capitalization instead (e.g. LeftDate, EmployeeName, etc.)

 

[syedadnan]

This sounds like it can be solved purely by SQL and not VBA. For example, iii would be this:

SELECT Month([Left date]) AS MonthLeft, Year([Left date]) AS YearLeft, COUNT([Left Date])
FROM YourQueryNameHere
WHERE IsNull([Left date])=False
GROUP BY Month([Left date]), Year([Left date])

iv would require probably a sub-query or two. And you've not provided a clear description of what "strength" is so, I would need more information on that for i and ii.

Before proceeding however, I advise you rename your field names to eliminate spaces. Having them just makes coding a little harder. Use capitalization instead (e.g. LeftDate, EmployeeName, etc.)

Thanks for the reply indeed ... ok leave it now what i want is dcount formula with below field names.

Field names are;

JoiningFrom: which is actually (Joining date)
Department:
Branch: Which is sub-department
EmpID: Which is employee ID

required dcount formula 1:

count EmpID through a form named "Turns" with text box named i) fromdate ii) todate and the dcount should see first the department then branch.

Else will let you know if required and more help