3 Replies Latest reply on Apr 26, 2013 8:29 PM by philmodjunk

    Tricky date calculation in relationship.



      Tricky date calculation in relationship.



           Hi Team,


           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