I'm trying to make a database to deal with crew changes. Each person has a back to back and specified rank. The date on for person1 is the date off for person2 when they are back to back. I need to be able to change the number of days or dates and have it recaluculate down the line up to a year.
I'm having a bit of trouble on how to structure my tables and how do automatically update the dates when either the days onboard changes or the dateOff Changes.
I need to have a crew list, where each person has a back to back replacement. Once I enter the names/info then there needs to be a schedule for each person where the day on of person1 is the same as the day off of person2. The schedule needs to extend for a year or so recalculating the dates if any of the dates on or off change down the line.
So person1 arrives on board say jan1st and his replacement is due to come on feb1st... I want to calculate future crew change dates based on a number of days onboard. Standard number of days is 35, but if something change I would like to be able to enter the number of days and it will calculate the future dates along the line.
I think you'd need to write a procedure that asks for the number of days and then adds data to a roster table with the entered interval. You would also need some way of determining who is 'next in line', which might simply be a sort order on a field in the personnel table.