Bradford Factor

36372

New member
Local time
Today, 12:24
Joined
Jul 6, 2012
Messages
8
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 like

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
FROM Absence
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 skillset.
Thanks loads
Andy
 
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 skillset.
Thanks loads
Andy

Sounds to me like you have it most of the way figured out. I threw something together real quick and it seems to do what you want:

Code:
SELECT tblStaff.StaffName, tblAbscence.AbscenceType, Sum(tblAbscence.AbscenceLength) AS SumOfAbscenceLength, Count(tblAbscence.AbscenceStartDate) AS CountOfAbscenceStartDate, Sum([AbscenceLength])*Count([AbscenceStartDate]) AS BradFordFactor
FROM tblStaff INNER JOIN tblAbscence ON tblStaff.StaffID = tblAbscence.StaffIDFK
GROUP BY tblStaff.StaffName, tblAbscence.AbscenceType
HAVING (((tblAbscence.AbscenceType)="sick"));
Which gives me:
Code:
[FONT=Calibri][COLOR=#000000]StaffName[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]AbscenceType[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]SumOfAbscenceLength[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]CountOfAbscenceStartDate[/COLOR][/FONT][FONT=Calibri][COLOR=#000000] BradFordFactor[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Dick[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]Sick[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]1[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Harry[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]Sick[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]3[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]2[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]6[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Tom[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]Sick[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]6[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]2[/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]12[/COLOR][/FONT]

HTH
 
If you reqally want to impress your HR manager, you may want to add simple facilities for calculation of the equally important DCF (Dilbert Correlation Factor)
 
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.


Hi Andy,

are you struggling with getting the actual result onto a form? So for all intents and purposes, you want the Bradford Factor to be calculated for each record viewed on a form?

thanks

Nigel
 
Hi Nigel,
I wanted the calculation to update a field in the staff table. Its ok though, I figured it out. Using a couple of update queries, create table query.

Cheers Adam, that was exactly what i missing. Got it working after looking through your sql

Thanks all for the help!!
 
If you reqally want to impress your HR manager, you may want to add simple facilities for calculation of the equally important DCF - Dilbert Correlation Factor)

Definitely going to add this into the database as soon as I've sorted out the other thing i'm working on.
 

Users who are viewing this thread

Back
Top Bottom