SUM() Function is adding non existent values to the total . . .

[CodeNoobster]

Hi everyone, i have two tables, one for employee absence and another for employee overtime. Now for one employee in particular, he has been absent for 3 days and has worked overtime for one day. Now the respective values for both are 28.5 and 3.5 in terms of hours if I calculate the sum manually without sql based on the data from the database.

Now when I query this information and use the SUM() function to get the total for all hours when absent and hours for overtime, the result is 28.5 and 10.5.

28.5 represents the total number of hours absent from work and 3.5 (and 10.5 apparently) represent the total hours of overtime. Now why would the total for absence be correct and the total for overtime have 7 hours of non existent overtime?

Here's my query :

SELECT SUM(Emp_Absence.emp_Absence_work_hours) AS Total_Absense, SUM(Emp_Overtime.emp_overtime_hours) AS Total_Overtime

FROM ((Employee INNER JOIN Emp_Absence ON Employee.emp_num = Emp_Absence.emp_num) INNER JOIN Emp_Overtime ON Employee.emp_num = Emp_Overtime.emp_num)

WHERE (Employee.emp_fname = 'Timothy') AND (Emp_Overtime.emp_overtime_date LIKE '%October%2014') AND (Emp_Absence.Emp_Absence_date LIKE '%October%2014')

Is the query incorrect? if so, how come the result for absence hours is valid?

Help and suggestions will be greatly appreciated.

[Rabbit]

The 3 absent records are matching up to the 1 overtime record. Which means the overtime record is being returned 3 times. Calculate the sums separately and join the results together so there's only one record per employee

[CodeNoobster]

Thanks rabbit, I just tried what you had suggested and it works.