Script to check if record exists, and create it
Each employee must be trained on any new version of a Standard of Operation (SOPs) document.
SOPs Table. Lists the SOP version numbers.
Employee Table. Lists each employee. Related to Employee Training Records Table by EmployeeID field.
Employee Training Records Table. Contains the SOP version number and date employee passed it off.
What I'd like to do is create a script that when a new SOP version number is added to the SOPs Table will check if each employee in the Employee Table has a training record in the Employee Training Records Table containing that SOP version number, and if the employee does not have a training record for that SOP, it will create one. I can't, for the life of me, figure out how to cycle through each employee in the Employee Table to check in a related table (the Employee Training Records Table) for a record. Is this possible? Am I insane? Have I described this well enough?
Also complicating things is that I only want to add a SOP training record for employees that have an "Active" status (contained in the Employee Table).