Tricky date calculation within a relationship

[bigjnz]

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,

 

 

[Ocean West]

Welcome to the forums!

 

Here is an example..

 

treatments.fmp12.zip