By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,462 Members | 799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,462 IT Pros & Developers. It's quick & easy.

Sum columns in a report

P: 21
Good Day All, I have a report that shows the hours that are worked by employees, who get paid on a biweekly basis. There is a "Total Hours Worked" column that is further divided into "Week1" and "Week2." It looks like this example:

[HTML]Total Hours Worked
Week1 Week2
8.00
8.00
9.00
9.00
10.00
3.50
6.00
6.00
6.00
6.00
---------------------
44.00 27.50[/HTML]

The reason I have the "Total Hours Worked" column divided into Week1 and Week2 is because I want to be able to calculate whether an employee has worked more than 40 hours per week for Overtime purposes.

The control source for the Week1 work hours is a field named txtWeek1 and here is the code that I use

Expand|Select|Wrap|Line Numbers
  1. =IIF([DateIn]-Forms!frmDates4Pay.txtDateBegin<7,(Format([TimeWorked],"#.00")),"  ")
And the control source for the Week2 work hours is a field named txtWeek2 and here is the code that I use

Expand|Select|Wrap|Line Numbers
  1. =IIF([DateIn]-Forms!frmDates4Pay.txtDateBegin>6,(Format([TimeWorked],"#.00")),"  ")
The code described above works just as I expect. However, I want to be able to add the columns separately, and my problem is that the fields that I am using to calculate the sum of the time worked is calculating both weeks for each field. I am using a simple Sum Statement for both total fields:

Expand|Select|Wrap|Line Numbers
  1. =Format(Sum([TimeWorked]),"#.00")
Is there a way to sum only those hours under Week1 in a total field separately from the hours in Week2? Those fields are named txtSumWeek1 and txtSumWeek2. As it stands now, both of these fields has the two weeks total displayed. FYI, the user inputs the payperiod begin and end dates from a form named frmDates4Pay and the click event to preview the form filters all days worked to between date begin and date end of the payperiod.

I am running XP and using Access 2002 in 2000 mode.

Any assistance will be greatly appreciated,

David
May 8 '07 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,476
You've posted this in the Access Articles (rather than Access Forum) section.
I will move it across for you.

MODERATOR.
May 8 '07 #2

P: 21
Thanks NeoPa, I recognized the mistake as soon as I hit the submit button and re-posted it myself int he appropriate Forum.

David
May 8 '07 #3

NeoPa
Expert Mod 15k+
P: 31,476
Right you are David. Thanks for letting me know :)
I'll lock this thread then to avoid confusion. Your other one will be the one to continue.
May 8 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.