AnsweredAssumed Answered

Tricky date calculation in relationship.

Question asked by JoshHyman on Apr 24, 2013
Latest reply on Apr 26, 2013 by philmodjunk


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