I am trying to build a database in which I can track the progress of multiple employees in training. Trainees follow a schedule in which they complete a training manual, rotate through three manufacturing lines, and complete a certification assessment before being placed where needed. The whole process should take about 5 weeks. The challenge is, there is usually a difference between what is planned and what actually happens. I need a database that will help me see at a glance where each trainee currently is in their progress and projected dates for when they should move on to the next phases, and the projected dates must update dynamically as I record trainee progress.
If we lived in an ideal world in which everything goes according to plan, all our trainees would follow the following timeline:
- StartDate (employee begins training manual)
- Rotation1 = DateAdd("d",14,[StartDate])
- Rotation2 = DateAdd("d",7,[Rotation1])
- Rotation3 = DateAdd("d,7,[Rotation2])
- Certification = DateAdd("d",3,[Rotation3])
- Placement = DateAdd("d",7,[Rotation3])
This is the planned timeline that would be calculated from the trainee's start date. However, because things do not always go according to plan, there can be a difference between planned and actual dates. I need to be able dynamically update all future dates based upon actual dates. For example, if a trainee spends 3 weeks completing step 1 rather than 2 weeks, I need all future dates to recalculate based upon the actual (not planned) date.
I've been racking my brain to figure out how to make this work. I've tried calculated fields in a query to calculate future dates, but I cannot replace the calculations with actual dates. I've also tried creating a form in which the default values for the date fields are based upon expressions, which could then be replaced with actual dates, but the default values don't appear in Form View. I've also tried using macros to alter the fields based upon values in calculated controls.
Do you have any advice that could steer me in the right direction?
Thanks!
Ben