Hey Heather, what matthew is suggesting is that the Employee test record will now be used only as a placeholder for tests an employee needs to stay current on, and a place to summarize the last time a renewal was completed.
So lets say you're adding a new employee and their tests. The workflow would be as follows.
Create the employee record>click "Add Employee Test">populate the Employee Test information and select the related Test type, (you would create one for each of the test types the employee needs to stay current on.)
You would want to create a new table called something like "Renewals" and relate it to Employee tests where one Employee Test can have multiple "Renewal Records"
The only function this renewal record serves is being able to capture a history of the employees completed renewals for each specific test, and (the most important part) a summary of the last (or Maximum) completed renewal for each specific test type for that employee, rather than the lastest of any completed tests.
There are some other great things this structure allows you to do, such as the flexibility of how often each test type needs to be renewed (annual, bi-annual, etc) or a field to indicate how much notice you want for each renewal (maybe one test requires more preparation), if the tests require a facilitator, you might benefit from a report showing all employees summarizing the number of upcoming renewals grouped by test type so you could schedule a group of employees that need that renewal for a single facilitator...
Hope this helps.
