calvin peters 1/3/2024 7:54:35 AM I'm looking at this a bit blurry-eyed on a phone screen so perhaps
I've missed something in your diagram, however it looks
like your table 'B' and 'C' are collecting basically the same information
but for 2 different departments; IT Dept and HR Dept.
If that's the case then the relationship becomes only 2 tables
with a filter on the table 'B' which filters by Dept and stores the
associated values from 'A' which appears to be Employee specific data.
So i suppose a 1-to-Many relationship between Employees and Positions
Where 1 Employee can select from Many positions and the
Position List can display a detail table of Employee records below it
that use it sorted by whatever criteria required for your use.
Since HR and IT seem to share job titles a third table for available departments with a 1-to-Many relationship to be used to enable filtering on Positions table
...or a simple drop-down on Employee table that allows choice of available departments to use as a filter for Positions.
That ought to gove you an Employee record with Department, Position
and a Position record with a list of all Employees who ever held it in a
table with Date of Hire, Active/Inactive Status if you wish, Department, etc.sorted
based on your desired criteria.
A summary column of the employee records on the Position Table (filtered using your preferences) would/ should allow for automating ...I assume payroll or recruiting... feedback loop for FILLED/VACANT status of any position in that table...I think...
If an Active Employee record is assigned a DIR position for IT (filter based on ACTIVE STATUS =true and POSITION = DIR and DEPARTMENT;= IT; then the position is FILLED otherwise POSITION is VACANT by virtue of the fact that a summary column
can display the EMPLOYEE number when the filter criteria are met.
A periodic trigger should handle the update fine with a nightly update since this type of
update is less time sensitive in terms of realtime accuracy...
Set the trigger to run DAILY if EMPLOYEE NUMBER is not null and
The FILLED/VACANT boolean can be used to trigger whatever dependant action you wish from there.
... assuming I understand the problem correctly from the diagram...
| 
