I have a simple time clock database. It includes
table called "tblEmployeeTime" with TimeID, dayName, TimeIN, TimeOUT, BadgeNum fields.
query called qryTimeClockHours that pulls the TimeID, dayName, TimeIN, TimeOUT, BadgeNum, and calculates the Minutes. I then convert these minutes to hours. This part works great.
The last part I can't seem to finish is I want to make a REPORT that shows the hoursWorked for each BadgeNum (employee) within a given time frame. I have a form that can choose the badgeNum and date range, but just cant figure out how to get these hoursWorked.
I thought there was a way to do it just using the report, but the SUM option is greyed out. So I went back to the query and there is no SUM in the total drop down.
Here is the query SQL code
table called "tblEmployeeTime" with TimeID, dayName, TimeIN, TimeOUT, BadgeNum fields.
query called qryTimeClockHours that pulls the TimeID, dayName, TimeIN, TimeOUT, BadgeNum, and calculates the Minutes. I then convert these minutes to hours. This part works great.
The last part I can't seem to finish is I want to make a REPORT that shows the hoursWorked for each BadgeNum (employee) within a given time frame. I have a form that can choose the badgeNum and date range, but just cant figure out how to get these hoursWorked.
I thought there was a way to do it just using the report, but the SUM option is greyed out. So I went back to the query and there is no SUM in the total drop down.
Here is the query SQL code
Code:
SELECT tblEmployeeTime.TimeID, tblEmployeeTime.DayName, tblEmployeeTime.TimeIN, tblEmployeeTime.TimeOUT, CStr(DateDiff("n",[Timein],[timeout])) AS Minutes, tblBadgeID.LastName, tblBadgeID.FirstName, tblBadgeID.BadgeNum, (CStr([Minutes]\60)+":"+Right("0"+CStr([Minutes] Mod 60),2)) AS HoursWorked
FROM tblBadgeID INNER JOIN tblEmployeeTime ON tblBadgeID.BadgeNum = tblEmployeeTime.EmployeeID
ORDER BY tblEmployeeTime.TimeIN DESC , tblEmployeeTime.TimeOUT DESC;