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

# Tricky date calculation in relationship.

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

• ###### 1. Re: Tricky date calculation in relationship.

One method would be to include two date fields in each treatment record to record the range of dates for which that status is in effect. Then a calculation field in the same table can compute the difference of the two dates to record the total days in that status on that record.

Then a summary report based on the treatment table can report total days in each status for a given performer, a group of performers, all performers and for a specified range of dates or over the entire range of data in your table.

• ###### 2. Re: Tricky date calculation in relationship.

Thanks Phil,

Such a simple soulution, thank you, it always amazes me how hard it is to see the simple answer when you think it needs to be complex.

Cheers

J

• ###### 3. Re: Tricky date calculation in relationship.

I've had the same thought a few dozen times myself.