Calculated Field (1 Viewer)

[MaleNurse]

I have a form called frmPeople on which is a sub form called sfrmBradford (This is in datasheet view.) This is to calculate a thing called the Bradford Sickness Absence Score. which is the number of occurrences in the last 12 months squared times the number of days absent.

For example O is occurances and D is days absent so.. 2 occurrences of five days each will be O^ x 10 or 4 x 10 = 40. This is a rolling calculation over the preceding twelve months.

On the sfrmBradford are the fields First Day Off and Date Returned (both dates) and a Datediff calculation for the number of days absent.

I would like to have, on the frmPeople, a calculated field that shows the running Bradford Score for an individual.
The pic at the bottom is a mock up of what I need.

Any help would be greatly appreciated.

 

[Gasman]

You could use a DCount() to get the number of occurrences within the desired period.
Then Dsum() for the number of Days within that period.
Then do the math.

You could also use a recordset for all the relevant records and do the count and sum and math.

I would create a dedicated function to do it more than likely.

 

[CJ_London]

You need to clarify what 'last 12 months' actually means.

Say today is 1st May. A year ago a person (I presume this is a calculation per person) was on sickness absence from 28th April and returns on 3rd May.
Should that be included in the calculation? If so what number of days? 5 days or 2 days?

Similarly what about someone who went sick on 30th April this year and is still absent today. Do you include the period or not in your calculation?

You need to know these things because they will impact the criteria you use to determine the values to go into the score calculation

 

[MaleNurse]

So, Say today was (is) the 5th of May 2023. The calculation hast to be the previous 12 months back to the 5th of May 2022 on a rolling bases so tomorrow it will go back to the 6th of May 2022 etc. Any sickness absence that was in progress at the time of this date would be ignored.

Its total days not weekdays so if I am absent from the 24th of April to the 1st of May the count would be 7 days.

If they are currently sic then it would be a date diff between the first day of sickness and today() what ever today is

 

[Gasman]

If they are currently sic then it would be a date diff between the first day of sickness and today() what ever today is

You could use the NZ() function and todays date for that issue.

 

[CJ_London]

OK - so the key date is the start date

and when you say

Say today was (is) the 5th of May 2023. The calculation hast to be the previous 12 months back to the 5th of May 2022

which day are you ignoring? today or the 5th May last year? Otherwise your calculation is based on a year and a day