# Tricky date calculation in relationship.

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

### 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