3 Replies Latest reply: Oct 30, 2018 6:22 AM by Shahbaz Khan Mohammed RSS

    Many to Many Linked Tables

    Daniel Emery

      Sorry for the ambiguous title, but I don't know the right words to use.

       

      I have an issue while linking three tables where one is missing a necessary unique identifier.

      The first table includes:

      Person ID(unique to each employee)

      Job ID(unique to each job posting)

      Application Date(Date the employee applies)

      Table 1.PNG

      The second table comes from a different system, but links easily with the same fields:

      Person ID

      Job ID

      Hire Date(Date which the employee accepts an offer)

      Table 2.PNG

      The issue comes with linking in the third table:

      Person ID

      Start Date(Day the employee starts in that job)

      Table 3.PNG

       

      I need to find some way to identify the start date for each Job ID as the first Start Date for that employee that comes after the hire date of the job. So:

       

      Table 4.PNG

      Any help would be appreciated.

       

      Thank You,

       

      Dan