Summed Hours Worked in Report

[krichard]

Hello..

Hopefully this is my last question...

I would like to sum the total hours worked for each employee in a report... right now my report is displaying the total hours for each day, so I have Eric listed 5 times because he worked 5 days, and his hours for each day are listed next to his name... i just want his name to show up once, with his total hours... then the same thing for each employee... i made a query that calculates teh total hours for each day they work, but now I need to calculate the sum of hours all together for that employee...

Expr1: HoursAndMinutes([TimeOut]-[TimeIn])

that is my code for total hours for that one day... in my query.

thanks again. kim

 

[GingerR]

Have you tried making a TOTALS query based on your existing query, grouped on Name and SUM of HoursAndMinutes?

By the way, i doubt this will be your last question. After 5 years I'm still asking questions )

 

[krichard]

hmmm.. can you explain to me how i would go about doing that?

Thank you!

 

[lespaul]

Sure...say that your first query is named qryFirst. You will want to do something like this:

Select Name, SUM(HoursAndMinutes) From qryFirst GROUP BY Name

Leslie

 

[krichard]

SELECT EmployeeId, Sum(HoursAndMinutes) As EXPR1
FROM TimeSheetQuery
GROUP BY EmployeeId

Error: Datatype mismatch in criteria...

This is the calculation in my TimeSheetQuery

Expr1: HoursandMinutes([TimeOut]-[TimeIn])

I want to add up the total hours worked for each employeeID #... So I made a new Query, and entered the code aboe.

Not sure if this is correct...

 

[GingerR]

In TimeSheetQuery, how about replacing "Expr1" with something more descriptive, like "EmployeeHours"?

Then your sql for your totals query would be:

SELECT EmployeeId, Sum(EmployeeHours)
FROM TimeSheetQuery
GROUP BY EmployeeId

 

[krichard]

SELECT EmployeeId, Sum(EmployeeHours)
FROM TimeSheetQuery
GROUP BY EmployeeId

I tried that and i am still getting the

Error: Datatype mismatch in criteria...

 

[GingerR]

ok so your orig query works fine? When you run it, does the EmployeeHours come out as a number (it will be right-alinged)? if not, change the expression to

cdbl(HoursandMinutes([TimeOut]-[TimeIn]))

 

[krichard]

It does come out as a number... actually a time...

i have 8:00 for the amount of hours/minutes past... left aligned.

now i just want to add the hours up for each employeeid..

 

[GingerR]

geez this shouldn't be that hard!!

What is "HoursAndMinutes" that you mention in your first post? is it a function you wrote?

make a brand new query.
bring in your existing query into it.
bring down the NAME and whatever field you have that does the time subtraction.
from the menu select VIEW+TOTALS.
in the field where you have your time subtraction field, change GROUPBY to SUM.

does that work?

 

[PaulBricker]

I don't mean to butt in here, but you all may be making this harder than you need to. If the original query worked, then why not just use the sorting and grouping capabilities right in the report already. Add a Group header that groups on Employee and then add a Group Footer that sums everything for you. It will create individual sums for each employee.

Paul

 

[krichard]

Hi Paul..

I have my employeeId in the group header, and I created a group footer, and i entered this calculation to total my hours for that person

=Sum(TimeSheetQuery!EmployeeHours)

and i get the following error:

datatype mismatch in criteria expression

thanks for your help..

 

[lespaul]

could you post the HoursandMinutes function?


Leslie

 

[krichard]

Option Compare Database
Option Explicit

Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
'*********************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*********************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)
End Function

Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
'*********************************************************************
' Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed in days between a starting Date/Time and
' an ending Date/Time formatted as a string that looks like this:
' "10 days" or "1 day".
'*********************************************************************
Dim interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays = IIf(days = 1, days & " Day", days & " Days")
End Function

 

[PaulBricker]

I haven't read all the posts, but as a general rule you would use a calculation like this.

=Sum(Expr1)

or

=Sum{[TimeIn]-[TimeOut])


one of those should do it for you.


Paul

 

[GingerR]

Looks like your function is returning HoursAndMinutes as a string, not as a number. That's why it cannot SUM it.

 

[lespaul]

that was my first reaction too Ginger!!

Leslie

 

[GingerR]

Kim--you'll have to fiddle around to make that a number. Either just subtract (TimeOut-TimeIn) if that result has whatever rounding in it that you want, or create an additional function which rounds to what you want.

I'd just do (TimeOut-TimeIn), sum it up, THEN apply the function in my report's control so it looks like how i want it. Will that work?

 

[PHV]

As the minutes are always 00, simply sum the hours:
=Sum(Val(TimeSheetQuery!EmployeeHours))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[krichard]

Hi Guys...

i really appreciate everyone's help on this...

the function above...
=Sum(Val(TimeSheetQuery!EmployeeHours)) just returns 0's

I have this formula below working on a form, with a subform, for when I am entering the employees hours...

=[TimeSheet Subform].Form!SummedHoursMinutes

and this works fine, but I haven't been able to get anything like this to work in my report...