Results 1 to 7 of 7
  1. #1
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26

    Employee(s) and their Position(s)

    Hey All,



    I have a quick question regarding my database design. I am developing a database which tracks Employees in various different positions across an organization. I have opted for a many-many between EMPLOYEE and POSITION due to the requirement that historical records are kept for each Position and the Employees that have previously occupied it.

    There is also a new requirement to track Candidates being considered for each position. My thought is that this many-many will handle Candidates well also. I plan on having all Candidates added to the same table, and have the DB recognize them as candidates if the record's StartDate is null.

    Any feedback on whether or not my reasoning is sound for going with a many-many would be appreciated. I know many prefer to use HISTORY tables, but I think that in my case this makes sense?

    Thanks!
    Skid

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i would say in addition to the tEmployee table , you need a subtable: tEmpPositions
    EmpID, Position, StartDate, EndDate

    to keep historical record of that employee. Use a subform on the tEmp master form. (1 to many)

    but to track candidates, many to many, that's a different table.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    There is also a new requirement to track Candidates being considered for each position. My thought is that this many-many will handle Candidates well also. I plan on having all Candidates added to the same table, and have the DB recognize them as candidates if the record's StartDate is null.
    that is one way to do it which should work - the only problem is you don't know when they were a candidate historically so you can't answer a question like 'when was this person last considered/applied for this/a role'

    If you use the end date for this purpose you are then getting into a difficult area where a value could have more than one meaning.

    Personally I would either have a separate table for considerations/applications or a new field in the positions table to indicate 'status' - active (they got the job), applied/considered/rejected etc. You haven't said but presumably you would want a field to state why someone did or didn't get the job.

  4. #4
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26
    Thanks for the input, ranman. I'll look into building some of that out.

    Thanks!
    Skid

  5. #5
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26
    I appreciate the insight Ajax. I do have 'DateResumeSubmitted' and 'DateResumeApproved' fields in the join table (Employee_Position) to provide further insight into when the Candidate was considered. I am also using a Status field in the joining query which uses a calculation comparing the Start/ End Date values with Date() to determine if the person is a Current, Past, Future employee, or a Candidate.

    My thought process in having this combined into one table is that even once a Candidate is hired, it will be simple to see all related data between that Position and that Person (e.g. Position_ID, Person_ID, StartDate, EndDate, DateResumeSubmitted, DateResumeApproved, . . .). Also, the user will only need to add a Person once, rather than Adding them as a Candidate to the position, then adding them as an Employee.

    Given this, do you still advise using a separate Candidate table? I know you are much more advanced in Access than I am so appreciate the feedback!

    Thanks!
    Skid

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no, same table is OK if you have the necessary fields

  7. #7
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26
    Thanks for the help!

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

Similar Threads

  1. Replies: 1
    Last Post: 08-12-2019, 12:39 PM
  2. Replies: 15
    Last Post: 05-12-2017, 04:09 AM
  3. Replies: 2
    Last Post: 05-09-2017, 04:12 PM
  4. Replies: 1
    Last Post: 04-14-2014, 11:44 AM
  5. Replies: 8
    Last Post: 06-06-2012, 12:28 PM

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