Variance/Standard Deviation

  • Thread starter Thread starter RA
  • Start date Start date
R

RA

I may be in the wrong forum but here we go..


I want to have my report highlight/change font color, or
some other type of identification, when the calculation
being reported falls out side a moving standard.

For Example:
Month 1:
Joe worked 2 hours, Mary 10 hours, Jill 12 hours, Harry,
11 hours
Month 2: Joe worked 12 hours, Mary 9 hours, Jill 7 hours,
Harry 9 hours

The report would highlight Joe's hours in month 1 because
it is outside the norm. but no highlighted hours because
no hours are far outside the norm.

RA
 
RA:

Well doing this would be a bit of work with multiple queries.

1.) Create a query which computes the average and standard deviation for
hours in a given month, group by month.
2.) Create a separate query that calcs each persons total hours per month.
3.) Include both queries in a third query where each persons hours compared
to the average and StdDev
4.) Add a field to your query where you calc what ever you want to highlight
stddev wise where it simply returns a true false as in:

OutOfBounds: IIF(SumOfHours > ([AvgHours] + (StdDevOfHours *2)) or
SumOfHours < ([AvgHours] + (StdDevOfHours *2)), -1, 0)

Then in your report add code like this in the on print event of the detail
section

If Me.OutOfBounds = True Then
Me!txtHours.ForeColor = 255 'red
Else
Me!txtHours.ForeColor = 0 'black
End if
 
Back
Top