The Bradford Factor
For those who don't know what the Bradford Factor is, its not another stupid reality tv show, or the title of john le carre
'esque spy novel. I was hoping for the second when my HR manager asked me if I knew what the Bradford Factor is?
Any way I digress... I do that a lot.
The Bradford Factor is a way of scoring the staff and organisations' sickness levels/quantity/ratio/something to do with
giving people a bollocking, for taking the Mickey, and trying to scive off work to often.
Any way the calculation goes something like this :-
a. number of periods of absences due to sickness
b. total time taken off sick in the year (number of days)
bradford factor = a*a*b
so basically if a staff member is genuinely sick, and takes a week off work they would get a score that looks like this :-
a = 1 (because they have only taken off 1 working week this year)
b = 5 (because they took 5 working days off)
bradford = 1*1*5 = 5
now if a person takes the same number of days off in a year but does it in 5 different periods (like every couple of Mondays,
cause they have a session on a Friday night, can't take their beer and are still feeling the effects of it Monday morning)
a = 5 (cause they call in sick at five different occasions)
b = 5 (because they take off the same number of days)
bradford = 5*5*5 = 125
you get it??
Anyway long story short, I made the HR manager a Staff database a couple of months ago, the kind of staff details type deal
Table - Staff – containing stuff like :-
ID number, name, role, address, other stuff like that.
Table – Disciplinary – containing stuff about complaints against staff etc :-
Disciplinary ID, Staff ID, Date, Details
Now she wants it to work out the Bradford factor for her.
Because she’s to busy to do it manually!
So I’ve added another table
Absence - It contains the following
absenceID - autonumber to catalogue all the absences
staffID - to link it to the staff member
absenceType - to differentiate between, holidays, sickness or compassionate leave
startDate - the first day of the period of absence
endDate - the last day of the period of absence
length - number of days taken off in that period of absence.
I now want to be able to work out the Bradford factor for each member of staff.
I included length field in absence table to store that part of the calculation.
So I thought I’d do a query that first Counts the number of times a staff ID is in the absence table :-
SELECT Staff.ID, Count(Absence.ID) AS CountOfID, Absence.Type
FROM Staff INNER JOIN Absence ON Staff.ID = Absence.StaffID
WHERE (((Staff.ID)=[?]) AND ((Absence.Type)="sick"));That gives me the ( a ) in my calculation
A second query then to get the ( b )
SELECT Absence.ID, Absence.StaffID, Absence.Type, Sum(Absence.Length) AS SumOfLength
WHERE (((Absence.StaffID)=[?]) AND ((Absence.Type)="sick"));So those queries get what I need to carry out the calculation.
But I can’t work out how to make access run them, store the values then carry out the calculation And return the Bradford
Factor to a field that I have created in the staff table, all in one go.
Any help would be much appreciated.
Do i even need to do it all in one go?
Did I mention that this isn’t for any type of assignment or anything like that I’m not in college, I’m 30, and I work for a
charity? So we don’t get the money to get things done professionally
I can visualise what I want to do but I just haven’t got the knowledge to pull it off. It’s a bit beyond my average IT guy