We help IT Professionals succeed at work.

Sum two different columns of the underlying query in a report

634 Views
Last Modified: 2013-11-28
Good Day All, I have a report that prints the time worked during the biweekly payperiod for the staff. The report distinguishes between the first and second weeks of the payperiod. There are 21 different pay/nonpay categories in the payroll. For example - Worked, Sick, Vacation, Holiday are examples of employees receiving pay for the day; however, No-Sick, LWOP, Tardy, No-Call/No-Show are examples of leave types where no pay was received.

My challenge is two-fold: I want to be able to sum separately the total of hours paid for Week1 and Week2, and to sum only hours for which the employee will be paid, but I still want the non-paid hours to be reflected in the report. The following is a rough sketch of how the report is laid out:

Total Hours Worked
 Week1      Week2
 8.00
 8.00
 8.00
 9.00
11.00
                  3.50
                  6.00
                  6.00
                  6.00
                  6.00
 ----------------
 44.00     27.50<-----txtSumWeek2
--^---txtSumWeek1

Here is a copy of the query:

SELECT tblMasterEmployee.LastName, tblMasterEmployee.FirstName, tblShiftsTimeWorked.WorkShiftHoursWorkedID, tblShiftsTimeWorked.DateIn, tblShiftsTimeWorked.EmployeeID, tblShiftsTimeWorked.TimeTypeID, Format(tblShiftsTimeWorked.TimeOut-tblShiftsTimeWorked.TimeIn,"Short Time") AS [Time], tblShiftsTimeWorked.TimeIn, tblShiftsTimeWorked.TimeOut, DateDiff("n",tblShiftsTimeWorked.TimeIn,tblShiftsTimeWorked.TimeOut) AS Minutes, IIf(tblShiftsTimeWorked.TimeOut<tblShiftsTimeWorked.TimeIn,[Minutes]+1440,[Minutes]) AS Minutes2, tblShiftsEmployees.LunchMin, tblShiftsTimeWorked.LunchMin, ([Minutes2]-tblShiftsTimeWorked.LunchMin)/60 AS TimeWorked, tblShiftsTimeWorked.Notes, tblLeaveType.LeaveType, IIf([DateIn]-Forms!frmDates4Pay.txtDateBegin<7,(Format([TimeWorked],"#.00")),"  ") AS Week1, IIf([DateIn]-Forms!frmDates4Pay.txtDateBegin>6,(Format([TimeWorked],"#.00")),"  ") AS Week2
FROM tblLeaveType INNER JOIN (tblShiftsEmployees RIGHT JOIN (tblMasterEmployee INNER JOIN tblShiftsTimeWorked ON tblMasterEmployee.EmployeeID = tblShiftsTimeWorked.EmployeeID) ON tblShiftsEmployees.[WorkShift-EmployeeID] = tblShiftsTimeWorked.WorkShiftEmployeeID) ON tblLeaveType.LeaveTypeID = tblShiftsTimeWorked.TimeTypeID
ORDER BY tblMasterEmployee.LastName, tblMasterEmployee.FirstName;

As it is now, all I am able to do is add all of the hours for both weeks into one of the text boxes. I want Week1's hours to sum in txtSumWeek1 and I want Week2's hours to sum in txtSumWeek2. Also I only want hours that are payable to sum, although I want both paid-hours and non-paid-hours to show in the report. I tried the following line which was suggested in an earlier post

=Format(Sum([Week1]),"#.00")

but I received an error that the expression was too complex. I tried that line in both the query and the report.

Any assistance will be greatly appreciated. FYI, I am running XP and using Access 2002 in 2000 mode.

Thanks in advance,

David

Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
I don't think this applies, but just in case you were using Format() as a way to extract the time portion of a date/time field, use TimeValue() instead.
(°v°)

Author

Commented:
Thanks harfang, so it appears that I need to add the payable field to the table, and I can have the program automatically turn it to true depending on the work/leave category. I'll work on this until I need to leave the office in the next 30 minutes. I'll keep you posted.

Thanks again,

David

Author

Commented:
Another Genius saves the day, I think. Man-oh-Man, sometimes all I can do is laugh when I learn something new in Access, as I am doing now. It appears after the first run that your suggestions worked for me. I added the "Payable" field to the Worked/Leave Description Table and to the employees' time worked/leave table. After selecting a work/leave category, I have an AfterUpdate routine that places the category Payable field into the employee worked payable field, so now I can check for payable. And then I added your Sum line for week1 and week2, and it appears to work, but a few minor things happened to my query after making the changes. I must run to a meeting, but I should be back later today for further testing.

Thanks again,

David

Commented:
Sure,

An alternate (and recommended) was of doing is this is *not* to have a field "payable" in the employee's work log, but use the field from "work/leave category". Unless, of course, the "payable" property might change in the future and you want to record the "payable" property as it was before the change, so as not to disrupt any accounting made on the table.

This is much like the difference between a "catalog price" in a table of products and the "unit price" in an order or invoice.

Anyway, keep me posted if you have questions.

(°v°)

Author

Commented:
Okay harfang, I am having a slight problem. Your suggestions does distinguish between the 1st and 2nd weeks' totals, but for some reason the time worked/paid leave is not printing in the Week2 column, although the Week2 total is adding up correctly. I have looked at the obvious areas as shown below

=Format([Week1],"#.00") ' text box for Week1

=Format([Week2],"#.00") ' text box for Week2

=Format(Sum(IIf([Payable],[Week1],Null)),"#.00") ' text box totals for Week1

=Format(Sum(IIf([Payable],[Week2],Null)),"#.00") ' text box totals for week2

Week1: IIf([DateIn]-Forms!frmDates4Pay.txtDateBegin<7,[TimeWorked],Null) ' Query column for Week1

Week2: IIf([DateIn]-Forms!frmDates4Pay.txtDateBegin>6,[TimeWorked],Null) ' Query column for Week2

This is the remodeled SQL:

SELECT tblMasterEmployee.LastName, tblMasterEmployee.FirstName, tblShiftsTimeWorked.WorkShiftHoursWorkedID, tblShiftsTimeWorked.DateIn, tblShiftsTimeWorked.EmployeeID, tblShiftsTimeWorked.TimeTypeID, tblLeaveType.Payable, (tblShiftsTimeWorked.TimeOut-tblShiftsTimeWorked.TimeIn) AS [Time], tblShiftsTimeWorked.TimeIn, tblShiftsTimeWorked.TimeOut, DateDiff("n",tblShiftsTimeWorked.TimeIn,tblShiftsTimeWorked.TimeOut) AS Minutes, IIf(tblShiftsTimeWorked.TimeOut<tblShiftsTimeWorked.TimeIn,[Minutes]+1440,[Minutes]) AS Minutes2, tblShiftsEmployees.LunchMin, tblShiftsTimeWorked.LunchMin, ([Minutes2]-tblShiftsTimeWorked.LunchMin)/60 AS TimeWorked, tblShiftsTimeWorked.Notes, tblLeaveType.LeaveType, IIf([DateIn]-Forms!frmDates4Pay.txtDateBegin<7,[TimeWorked],Null) AS Week1, IIf([DateIn]-Forms!frmDates4Pay.txtDateBegin>6,[TimeWorked],Null) AS Week2
FROM tblLeaveType INNER JOIN (tblShiftsEmployees RIGHT JOIN (tblMasterEmployee INNER JOIN tblShiftsTimeWorked ON tblMasterEmployee.EmployeeID = tblShiftsTimeWorked.EmployeeID) ON tblShiftsEmployees.[WorkShift-EmployeeID] = tblShiftsTimeWorked.WorkShiftEmployeeID) ON tblLeaveType.LeaveTypeID = tblShiftsTimeWorked.TimeTypeID;


I know I am overlooking something, but I can't figure it out. Any further assistance will be appreciated.

Thanks again,

David

Commented:
It looks fine to me, but I don't have your data to see if anything *should* get printed at all...

Try these three controls in your footer(s):

    =Sum([Week2])
    =Sum(IIf([Payable],[Week2],Null))
    =Sum(IIf(Not [Payable],[Week2],Null))

And tell me if they add up. Again, no need for the format function, simply format the fields, e.g. as Standard, 2 decimals, and you will be fine.

Just to make sure, in the detail section, add:

    Week2    (format: standard, 2 decimals)
    Payable   (checkbox)

This might also shed some light.

(°v°)

Author

Commented:
I'm not sure why Week2 would not print with the format function, but once I removed it and  followed your advice with formating it through the properties window, it worked fine. However the three totals controls in the footer gave me "#Error." I'm not sure why, and right now since my form is working, I will not pursue this any further unless you think I should.

Have a blessed day as you have blessed mine.

Peace and prosperity,

David

Commented:
Strange... Perhaps it has something to do with the confusion between fields and controls, if you have a control also called "Week2" for instance. But you are right, let's leave it at that if your form is working (and calculating accurately).

Thanks for your kind words, and success with your project!
(°v°)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.