Results 1 to 6 of 6
  1. #1
    pamelamoore27 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    1

    Need help with designing a database for Position Management

    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.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    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

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    To maintain history, need a junction table. Consider:

    tblPersonnelActions
    ID
    EmpID (foreign key)
    JobID (foreign key)
    ActionDate
    ActionType (hire, transfer, termination)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    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.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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.

  6. #6
    hansendl is offline Advanced Hobbyist
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    38
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help designing a database
    By docpanderson in forum Programming
    Replies: 4
    Last Post: 07-01-2014, 03:03 PM
  2. database designing
    By tommyried in forum Database Design
    Replies: 1
    Last Post: 02-27-2014, 12:41 PM
  3. Designing my database
    By ldypurple in forum Database Design
    Replies: 11
    Last Post: 03-06-2013, 10:58 PM
  4. Designing a database for a log
    By neo651 in forum Database Design
    Replies: 3
    Last Post: 10-31-2011, 11:45 AM
  5. Help with designing database
    By nimalp in forum Access
    Replies: 6
    Last Post: 09-15-2010, 10:34 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums