I mocked up your Time tables and used Employee 602
I omitted the Employees Table and used this code to get the Daily Billable Hours with WorkCode 12
Here are my sample tables
EmployeeId |
TimeCardid |
602 |
1 |
TimeCardIhoursId |
BillableHours |
dateWorked |
workCodeId |
TimeCardId |
1 |
8 |
20/01/2014 |
12 |
1 |
2 |
8 |
02/03/2014 |
12 |
1 |
3 |
8 |
24/02/2014 |
12 |
1 |
4 |
8 |
05/09/2014 |
12 |
1 |
5 |
8 |
05/12/2014 |
12 |
1 |
6 |
7 |
05/12/2014 |
23 |
1 |
7 |
5 |
23/03/2014 |
3 |
1 |
Code:
SELECT TimeCard.EmployeeId
, Sum(tblTimeCardHours.BillableHours) AS SumOfBillableHours
, tblTimeCardHours.dateWorked
, tblTimeCardHours.workCodeId
FROM
tblTimeCardHours INNER JOIN TimeCard ON
tblTimeCardHours.TimeCardId = TimeCard.TimeCardid
GROUP BY
TimeCard.EmployeeId, tblTimeCardHours.dateWorked, tblTimeCardHours.workCodeId
HAVING
(((TimeCard.EmployeeId)=602) AND ((tblTimeCardHours.workCodeId)=12))
It doesn't need DSum.
To get the total hours
Code:
Select EmployeeID, Sum (SumOfBillableHours)
From
(
The SQL above goes here
)
Group By EmployeeID;
You can add in your Employees Table if you want.
But try this sample and see how it works. I'll help with adding in the employees table, if you need help.
I called the final query "qryBillableTime".
EmployeeID |
SumOfSumOfBillableHours |
602 |
40 |