Need hoursWorked from a query - sum,total,running total? (1 Viewer)

foshizzle

Registered User.
Local time
Today, 11:26
Joined
Nov 27, 2013
Messages
277
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
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;
 

MarkK

bit cruncher
Local time
Today, 08:26
Joined
Mar 17, 2004
Messages
8,178
If your TimeIN was negative and your TimeOut was positive, you could simply sum all your Date/Times per BadgeID between dates and you'd be done.

I'd create a table like this . . .
tTimeLog
TimeLogID (PK)
BadgeID - identifies the person
Direction - minus 1 for IN, plus 1 for OUT
DateTime - the date and time
. . . and the SQL to sum time for a particular badge number for a month would be . . .
Code:
SELECT Sum(Direction * DateTime)
FROM tTimeLog
WHERE BadgeID = 12
AND DateTime >= #1/1/14# AND DateTime <= #1/31/14#
. . . so that has the net effect of subtracting all your date/time ins from your data/time outs and this leaves you with total time between ins and outs. Very simple.

You might be able to pull that kind of thing off using a UNION query but I'm not sure what kind of data is in your DayName field. You want the date/time in a single field, since then it is a single number, (and lets face it, date/time value is like dollars and cents, feet and inches, it's one dimension) and very easy to work with.
 

foshizzle

Registered User.
Local time
Today, 11:26
Joined
Nov 27, 2013
Messages
277
Hmm. Not sure how to get that to work.
I tried making a new query so not to mess with the original table since I'm not sure it's complete setup. I tried multiplying the TimeOUT field. TimeOUTNeg: ([TimeOUT])*-1 but thats not correct. I can only assume that its because TimeOUT is actually a time/date field? It just displays as hours such as 14:00, etc. Any other ideas?
 

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,611
...within a given time frame

From the sound of your structure, I don't think your table is set up properly to do this. Do you store a date anywhere in that table? Post some sample data from your table. Use this format:

tblEmployeeTime
TimeID, dayName, TimeIN, TimeOUT, BadgeNum

Be sure to seperate values with a comma
 

foshizzle

Registered User.
Local time
Today, 11:26
Joined
Nov 27, 2013
Messages
277
I have uploaded the table as text, csv. I dont really think I can change the original table structure without crashing my db. This timeclock was a template I found.
 

Attachments

  • tblEmployeeTime.txt
    1.3 KB · Views: 87

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,611
Your data looks good--I thought dayName was just "Sunday" or "Saturday" and didn't have a full date in it. Now, as long as you have a query that produces the individual results you want, then this is a report issue.

You should create a report based on that query that produces the individual results. Create a grouping section on BadgeNum field and include a totals row that sums the total hours. The key to this report is to not have a details section. You can still use your form to pass criteria but all that will show is the results at the BadgeNum level.
 

foshizzle

Registered User.
Local time
Today, 11:26
Joined
Nov 27, 2013
Messages
277
Perhaps I am missing something.
I dont get the option to sum on this report, even in design view. please see attached. The only option i get is to count the number of records. Even when I remove the detail section, it shows nothing
 

Attachments

  • rpt1.jpg
    rpt1.jpg
    100.4 KB · Views: 107

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,611
Create a control in the BadgeNum footer with this as the source:

=Sum(HoursWorked)

You already have a field in there for the Count, copy and paste it, then change the source to what I listed above.
 

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,611
That's because HoursWorked is a string that can't even be converted to a number:

(CStr([Minutes]\60)+":"+Right("0"+CStr([Minutes] Mod 60),2)) AS HoursWorked

I would make Minutes a numeric value in your query:

DateDiff("n",[Timein],[timeout]) AS Minutes

Then, in the report sum Minutes up and pass it through your Hours formatting function:

=(Sum([Minutes])\60)+":"+Right("0"+Sum(([Minutes]) Mod 60),2))
 

foshizzle

Registered User.
Local time
Today, 11:26
Joined
Nov 27, 2013
Messages
277
Thats it! that was the problem. Thanks! Im learning alot here
 

foshizzle

Registered User.
Local time
Today, 11:26
Joined
Nov 27, 2013
Messages
277
Im just having trouble formatting the sum.
I tried what you said
=(Sum([Minutes])\60)+":"+Right("0"+Sum(([Minutes]) Mod 60),2))

it says too many closing parentesis, i think Ive tried about every which way..
The Sum function is only working partly without this i believe
 

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,611
Remove the very last right parenthesis
 

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,611
Did you change your query for minutes? It needs to be a number not a string.
 

foshizzle

Registered User.
Local time
Today, 11:26
Joined
Nov 27, 2013
Messages
277
Yes sir

Code:
SELECT tblEmployeeTime.TimeID, tblEmployeeTime.DayName, tblEmployeeTime.TimeIN, tblEmployeeTime.TimeOUT, (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;

Screenshot also
 

Attachments

  • Capture2.jpg
    Capture2.jpg
    99.9 KB · Views: 79

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,611
In the report control, I misplaced the parenthesis. This should work:

=(Sum([Minutes])\60)+":"+Right("0"+(Sum([Minutes]) Mod 60),2))
 

foshizzle

Registered User.
Local time
Today, 11:26
Joined
Nov 27, 2013
Messages
277
I still cant get around it. I have uploaded the parts if you get a second to review them. Thanks in advance.

qryTimeClockHours
rptTimeClock
 

Attachments

  • employeetime.accdb
    444 KB · Views: 87

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,611
You need to change your pluses(+) to amepersands(&)

=(Sum([Minutes])\60) & ":" & Right("0"+(Sum([Minutes]) Mod 60),2))
 

Users who are viewing this thread

Top Bottom