I've inherited a database system designed to calculate sickness absence statistics. There is a main table with an employee ID [EMPLOYEEID] which also holds the number of days worked by that person in a year [thedaysworked]. There is then a table which records absences for each employee, it records the date of the absence etc and also holds the number of days absent [Number_of_days_absent]. Each time the employee is absent they will have an entry in this table linked by [EMPLOYEEID]. I have created a query which needs to total all these absence days, which works fine, however, what i need is to then total these by school and section for absences within the last year, i have also got this working without much effort.
-
-
SELECT School.[Type Description] AS School, Section.[Type Description] AS [Section], Sum(absence.Working_Days_Absent) AS [Working Days of Absence]
-
FROM [Employee Status] INNER JOIN ([Section] INNER JOIN (School INNER JOIN (Role INNER JOIN (KCSTAFF02 INNER JOIN absence ON KCSTAFF02.EMPLOYEEID = absence.employeeID) ON Role.RoleID = KCSTAFF02.ABSENCE_Role) ON School.School = KCSTAFF02.ABSENCE_School) ON Section.Section = KCSTAFF02.ABSENCE_Section) ON [Employee Status].[Employee Status] = KCSTAFF02.ABSENCE_Status
-
WHERE (((absence.[1st_day_of_absence])>Date()-365))
-
GROUP BY School.[Type Description], Section.[Type Description];
-
The problem arises when i ask it to work out how many people are in each school and section. I asked it to count [EMPLOYEEID] from the main table but this results in this ID being counted for each entry in the absence table, which results in a lot more people than expected! I also have the same problem with [thedaysworked] which adds itself for each entry in the absence databse, so if an employee has 5 absences this gets counted 5 times.Is there a way i can tell it just to count it once but still leave the other totals which are working alone?
This is what i currently have
-
-
SELECT School.[Type Description] AS School, Section.[Type Description] AS [Section], Sum(KCSTAFF02.thedaysworked) AS [Combined Days], Count(KCSTAFF02.EMPLOYEEID) AS [Employee Count], Sum(absence.Number_of_Days_Absent) AS [Days of Absence], Sum(absence.Working_Days_Absent) AS [Working Days of Absence]
-
FROM [Employee Status] INNER JOIN ([Section] INNER JOIN (School INNER JOIN (Role INNER JOIN (KCSTAFF02 INNER JOIN absence ON KCSTAFF02.EMPLOYEEID = absence.employeeID) ON Role.RoleID = KCSTAFF02.ABSENCE_Role) ON School.School = KCSTAFF02.ABSENCE_School) ON Section.Section = KCSTAFF02.ABSENCE_Section) ON [Employee Status].[Employee Status] = KCSTAFF02.ABSENCE_Status
-
WHERE (((absence.[1st_day_of_absence])>Date()-365))
-
GROUP BY School.[Type Description], Section.[Type Description];
-
