Any thoughts

[Guest]

Does anyone have any good ideas of how to track turnover in a database? I
have two options on how to run my database now, I can either mark an employee
as separated and change their employeeID to an out of range number entering
their information into the tblResignation and storing the info there, or
doing basically the same thing but instead of reassigning them an out of
range number, pulling them to the tblPastEmployees and deleting them from the
active database.

Currently I have these tables that would be involved:

tblEmployees: EmployeeID(PK), DateHired
tblResignation: EmployeeID(PK), LastDayWorked(PK)
tblPastEmployees: EmployeeID, DateHired, LastDayWorked

I have a query that will pull all information from the active tables that is
needed and place it in the tblPastEmployees if I go that route and delete it
out of the active database.

Any ideas of getting the turnover rate? I know I need to count the
LastDayWorked and the DateHired by a date range and divide LastDayWorked by
the number of employees. I think I would need to know the beginning number
of employees at the beginning of my date range, then know for that period how
many quit and do the division but am having a hard time of trying to get this
to work correctly.

 

[M Skabialka]

A simple beginning to resolving your problem would be to add an employee
status field to tblEmployees, e.g.:
A= active
T=Terminated (for any reason - you could add another field to describe the
reason)
so you wouldn't need two tables, tblEmployees and tblPastEmployees.
The same table could have DateHired and LastDayWorked, so your wouldn't need
tblResignation either.

However, if people leave and come back, then you would need to capture that
information as well!

This doesn't cover the second part of your question though, but you could do
your queries on one table rather than three!

Mich