Need help with designing a database for Position Management

[pamelamoore27]

I am new to Microsoft Access and need to develop a database to track positions within my company. To elaborate in more detail, I need to be able to identify all active positions within the company as well as inactive (vacant) positions. Then, I need to show which employee within my company currently holds that position as well as the movement when an employee vacates the position and either moves into another position or separates from the company altogether. This information would be used to calculate the vacancy and turnover ratio for each department and company-wide.



I guess I am a little puzzled as to what type of tables I need to setup and the content that should be contained in each. Currently I have a table set up for Employees, Jobs, Department, and Location. But, I'm not quite sure how to track the effective dates of when a position is vacated and when it is filled.

Any assistance that can at least get me started would be helpful.

[ranman256]

I would have the tEmp table with all the field you need for an empoyee
AND each emp would have [Position]

The list of positions would be in the tPosition table. These codes will fill the tEmp.Position field.

The unfilled positions would would be shown with an outer join query.
With all records in tPositions and where tEmp.Position = null

[June7]

To maintain history, need a junction table. Consider:

tblPersonnelActions
ID
EmpID (foreign key)
JobID (foreign key)
ActionDate
ActionType (hire, transfer, termination)

[NTC]

in addition to the advice you have so far; fundamentally you are tracking employees - - rather than just tracking positions

You need separate look up tables (lists) for: Employees, Jobs, Departments, and Locations

Then you need a table that is the History; which has each one of the those fields (all are Look Up type fields to their respective table/list) plus startDate and endDate fields

This is the correct structure and then using queries one will be able to identify open/filled Positions as well as any Employee's history.

[orange]

I'm not sure if I'm understanding Jobs -- is that what you're calling Position?
Your subject is Position Management but you don't seem to include Position as an entity???

I agree with NTC that you are fundamentally managing Employees.

[hansendl]

Fundamentally, he is tracking both employees AND positions. These are separate entities and must be tracked separately. An employee can exist independent of a position, such as when the employee leaves the organization. You don't necessarily want to delete the employee record once they no longer occupy a position--what if they come back, or what if you need to verify dates of employment? Likewise, a position can exist independent of an employee, such as is the case when a position is vacant. Thus, you need tables to track details for both employees and positions. When setting up your tables, you also need to be careful to distinguish what fields belong to employees, and what fields belong to positions. For example, you shouldn't set up your tables such that employees report to other employees; rather, you should set it up so that positions report to other positions. In addition, some contact info (desk phone number, etc.) might apply to the position, while other contact info (cell phone, email address) might apply to the employee.

You'll need a junction table to track the position history of employees:

tblEmployeePositionHistory
PrimaryKey
EmployeeID (foreign key)
PositionID (foreign key)
StartDate
StopDate
StopReasonID (foreign key--identifies reason code that employee is no longer filling position)

Personnel actions (promotions, pay adjustments, terminations, etc.) should be managed separately from position actions (recruitment action to fill a vacancy, abolishment of position, establishment of a new position, changing the position description, changing pay class, etc.), though some personnel actions drive a requirement for a position action. In addition, you may want to distinguish between position types and individual positions. For example, you may have several accountant positions in your organization that all use the same position description. In this case, it makes sense to have an Accountant position type.

This can get very complex very quickly--especially if you need to track funding sources for positions, deal with multiple departments/divisions, etc.! Good luck with your project!