Summing Sums in records...?

[Hrtlover]

Hi!
I've been trying to work on a database for a company that wants to keep track of Hours worked by the employees... without purchasing something like MYOB.

I have nearly completed the database, and as i was completing the final step, The reports. I wanted to add something that would calculate the hours for the record.. and then proceed to another record. and then at the end add up all the hours into a final sum that the employee had worked for the week.



I have worked out the way to add up for the one record. but how do you add it over multiple records.. If this makes sense?

I have attached a screen shot that might give more of an idea if it doesn't give a clear idea..

Thanks heaps!!!!

Brad

[jzwp11]

Since you want to have a sum for each employee, you have to add a grouping level based on the employee field. You will need a footer for that group and within the footer you would put a control for the employee's sum.

[llyon]

If the control for your record total is named HoursWorked, then in the footer for the group add a text box and set the control source to:

=Sum(HoursWorked)

[Hrtlover]

Thanks for the response.

=sum(hoursWorked) (in both DateWorked footer and FirstName footer)was the first thing i tried... It asks for a parameter. :\
Any other suggestions?
cheers
Brad.

[jzwp11]

What is the actual name of the control that holds the hours worked value in the detail section of the report?

Alternatively, could you post a copy of your database with any sensitive data remove?

[Hrtlover]

Sorry for the late reply.

Here is the file, Work your magic !!

thanks
Brad.

[Hrtlover]

Sorry for the late reply.

Here is the file, Work your magic !!

thanks
Brad.

Sorry! whilst i remember it, its report 2 that is the most up-to-date one.

[jzwp11]

The reason you could not get a sum is because of the format() function you used on the hours worked value. In adding the format, you converted the value to a text value and you cannot sum a text value.

Just a word about dates in Access. Access stores dates as a number with the integer portion representing the number of days since 12/31/1899. The decimal part of the number is used for the time, and represents the fraction of a day. For example, the date/time of 4/9/2011 11:00:00 PM is actually stored by Access as the number 40642.9583333333

What you basically have to do is find the difference between the start and finish times in minutes using the datediff() function and then divide by 60 to get the number of hours worked. You can then add up those hours. To display the value as xx:yy, you will need another control that is based on the hours determined. The short time format will not work on the hours you calculate, so you basically have to do the conversion.

The other thing is that you have to take in to account when someone starts work on one day and ends on the next (i.e. third shift) since you are storing only the start date and not the finish date. Since the finish time will be less than the start time, the datediff() function will not calculate the hours correctly. You will end up with a negative number that basically is equivalent to the hours that were not worked in a 24 hour period, so you have to add 24 hours to this negative value to get the actual hours worked. To solve this you have to check to see if the finish time is less than the start time by way of the IIF() function.

The other thing I noticed in your report was that you were grouping by first name. If two employees have the same first name; their hours will be grouped together. It is better to group by the employee ID field since this value should be unique to the employee.

In the attached database, I have made the necessary modifications. I also added a couple of controls that display the time worked (xx:yy format) and the sum of the time worked . BTW, what were you planning on displaying if the total time worked exceeded 24 hours? zz:xx:yy? where zz is the number of days, xx is the number of hours and yy is the number of minutes.

[Hrtlover]

jzwp11,

You are god!

Thanks a million for fixing that for me, i never would've worked out how to do that.. and i was messing around with DateDiff.:\

Also, thanks for explaining it, i might have to read it a few times to understand it (tomorrow as im 3/4 asleep right now :P)

But thanks a million!!

Brad

[ajetrumpet]

You are god!

jz,

You're God!! Does that title come with fries?? Does it come with "MVP" perhaps??

[jzwp11]

You are welcome. BTW, not god, I remember seeing something similar before.

[Hrtlover]

Actually, just a quick question, i forgot to add in there, that i need to remove a half an hour for a 'break' time for each record. What would be the easiest way to do this? a checkbox on the form? would that include another IIF statement? :| I wish i had've thought of this before.. sorry

[jzwp11]

Does the break time vary depending on the time worked?


You would have to alter this expression as well as the one in the group footer.

This is what is currently in the hoursworked control:
=IIf([workfinnishtime]<[workstarttime],24+DateDiff("n",[workStartTime],[workFinnishTime])/60,DateDiff("n",[workStartTime],[workFinnishTime])/60)

You can subtract 30 minutes as follows:
=IIf([workfinnishtime]<[workstarttime],24+(DateDiff("n",[workStartTime],[workFinnishTime])-30)/60,(DateDiff("n",[workStartTime],[workFinnishTime])-30)/60)

Or you can subtract 0.5 hours as follows:
=IIf([workfinnishtime]<[workstarttime],23.5+DateDiff("n",[workStartTime],[workFinnishTime])/60,(DateDiff("n",[workStartTime],[workFinnishTime])/60)-0.5)

[Hrtlover]

It will be done on a hourly basis. for every 5.5 hours worked, you lose .5 a hour in pay for a break..

Does that complicate things?

[jzwp11]

Well, it changes things since we cannot simply subtract 0.5 hours like I showed in my previous post; we have to account for any hours worked that are multiples of 5.5, so if someone works less than 5.5 hours they do not get the 0.5 hours subtracted, if the hours worked is less than 11 but greater than or equal to 5.5, then 0.5 hours is subtracted. If the hours worked is greater than or equal to 11 but less than 16.5 then 1 hour is subtracted and so on. In a terms of a general expression, this is what is needed

adjusted hours worked= hoursworked-(int(hoursworked/5.5)*0.5)

Doing this in the detail section of the report is accomplished by adding another control for the adjusted hours worked. However, doing it in the group footer will get messy since you have to substitute the actual expression for hours worked into the adjusted hours expression which will look like this:

=Sum(IIf([workfinnishtime]<[workstarttime],24+DateDiff("n",[workStartTime],[workFinnishTime])/60,DateDiff("n",[workStartTime],[workFinnishTime])/60)-(Int((IIf([workfinnishtime]<[workstarttime],24+DateDiff("n",[workStartTime],[workFinnishTime])/60,DateDiff("n",[workStartTime],[workFinnishTime])/60))/5.5)*0.5))

I've attached the amended database