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

    Tricky date calculation in relationship.

    JoshHyman

      Title

      Tricky date calculation in relationship.

      Post

            

           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