Circular Relationship of tables to display latest applicable reference fields

can a record in the personnel table have more than one job running (on the assignments table) at one time?

It shouldn't, if it does then someone has not ended an assignment when they moved them from one assignment to the other.    When this happens, currently, a number "2" shows as the number of active job assignments for that person which indicates that something needs to be changed.    

Ok, then here is what you could do: in the relationship between personnel and personnel assignments, create a summary field that will summarize the minimum of the record id# with a filter that tells you that it is an active job (from the personnel assignments table fields). This summary field will give you the record id# of the first active job assigned to that person, so if there are 2, and the earlier one is correct, we can use this record id# to display that job information on the personnel record. (You could do the inverse of this and do maximum of the record id# and always return the newest active job).

Now that we have that record id#, we can build a reverse relationship between the personnel and personnel assignments table, this time where a personnel assignment can have many personnel. (This is an action not performed very often and can seem complicated and counter intuitive.) When building this relationship, click the dropdown that would current be populated with something like 'related personnel assignment' and replace it with your minimum or maximum record id# field. Once you've created this relationship, you can then create all the lookup fields for that personnel record with all the job data found on that personnel record.

Let me know if you need any help with this. My contact info is in my profile.

Good luck!

OK- this is great!  I did the above without an issue and it works great.   I can look at my people and see not only who is available, but where each person is currently.        

Now, to go another step further.    Because different people want to see the information in different ways.     I have a table created from my "jobs" table that is my "locations" table.   Some of this information was used in the lookup fields for resolving my previous posted question.    However, this "locations" table is not related directly to my personnel table or my personnel assignments table.   Only back to the jobs table.  

When I open this locations table. I hope to be able to set it up to only show me locations with job status that is either active or upcoming, and display the associated job number, anyone assigned to that job number and their job title.  

I am thinking that I need to just relate the locations table to the personnel table because now it seems that all the information I need is in the personnel table.   However, because this personnel table looked up the location of the person through a separate relationship with personnel assignments I am not sure if it will work.    

I always create a separate copy when I am trying things like this, but it ends up taking me a long time of trying to find out that they way I did it is not the best way for future use, or it is not possible and I have wasted to much time on it.    Furthermore, I struggle with multiple interruptions constantly causing me to have to backtrack to keep track of exactly where I was and what I was doing next in the creation of these.