Tricky date calculation in relationship.
This one is doing my head in a little and I am stumped for the best approach.
So from the start....
I have a large Performer Health DB that I am making currently and it is based on an older access DB, the main function is to track injuries and treatments of those injuries.
Each Performer has a record, related to that are each performers Injury records, related to that are the individual treatments for those injuries.
Performers > Injury > Treatment.
When a performer has an injury they are treated and the treatment record is updated with a status that is either Restricted, Available or unavailable depending on the seriousness of injury.
What I am wanting to do is work out how many days total each injury has had the performer restricted and how many days total each performer was unavailable.
Each treatment record gets a treatment date auto entered, so I am using this for calculations through relationships to work out the total days, the relationships look for the oldest unavailable date and the newest available date and work out the difference, I am doing the same thing for restrictions.
My Problem is this.....
The above system works fine for fine when the treatment pattern is as below....
Treatment 1 - 1/1/2013 - Unavailable
Treatment 2 - 3/1/2013 - Restricted
Treatment 3 - 10/1/2013 - Available
The current calculations I have will work out days unavailable or restricted no matter what order they come in, ie Treatment 1 restricted and treatment 2 unavailable.
It fails however when the treatment pattern is a such...
Treatment 1 - 1/1/2013 - Restricted
Treatment 2 - 3/1/2013 - Unavailable
Treatment 3 - 10/1/2013 - Restricted
Treatment 4 - 13/1/2013 - Available.
What I need is a way to calculate the days total between status changes and then add them for an injury so treatment 1 = 2 days restricted, treatment 2 = 7 days Unavailable, treatment 3 = 3 days restricted, totalling 5 days restricted and 7 days unavailable.
Any help or ideas any can provide would be greatly appreciated,
I have included a basic test of the way I am doing things now