Need help with a complicated IF statement

[debbieg]

I am creating a database to track payroll information. Employees are assigned to a Home Dept but can work in other departments. I'm at the point where I'm trying to calculate overtime. I think I've got it figured out if they only work in 1 other department during a day. My problem is if they work in 2 or more other departments in 1 day.

Here's what it looks like on the form:

(on a subform)
Home Dept:  555
[b]
Date        Start1    Stop1     Start2    Stop2    Hours
[/b]
01/17/2005  08:00 AM  12:00 PM                        4
01/18/2005  01:00 PM  05:00 PM  05:30 PM  07:30 PM    6
01/19/2005  08:00 AM  12:00 PM  01:00 PM  05:00 PM    8
01/20/2005  08:00 AM  12:00 PM                        4
[COLOR=red]01/21/2005  01:00 PM  05:00 PM                        4[/color]

(on a subform)
Temp Departments 
[b]
Dpt Date       Start1    Stop1     Start2    Stop2    Hours 
[/b]
512 01/17/2005 01:00 PM  05:00 PM  05:30 PM  07:30 PM    6
512 01/18/2005 08:00 AM  12:00 PM                        4
512 01/20/2005 01:00 PM  05:00 PM  05:30 PM  07:30 PM    6
[COLOR=red]550 01/21/2005 08:00 AM  12:00 PM                        4
512 01/21/2005 05:30 PM  07:30 PM                        2[/color]

Here's what I've come up with that I think will work if there is only 1 other department:

If HomeWorkDate = TempWorkDate then

  if TotalHoursWorked > 40 then

    if isnull (HomeDeptStop2) and isnull (TempDeptStop2) then
      if HomeDeptStop1 > TempDeptStop1 then
        if PreviousRunningTotal + HoursWorked > 40 then
          if 40 – PreviousRunningTotal < 0 then
            OT = HoursWorked
          Else
            OT = 40 – PreviousRunningTotal
          End If
        Else
          OT = 0
        End If
      Else
        OT = 0
      End If
    End If
  
    if NOT isnull (HomeDeptStop2) and NOT isnull (TempDeptStop2) then
      if HomeDeptStop2 > TempDeptStop2 then
        if PreviousRunningTotal + HoursWorked > 40 then
          if 40 – PreviousRunningTotal < 0 then
            OT = HoursWorked
          Else
            OT = 40 – PreviousRunningTotal
          End If
        Else
          OT = 0
        End If
      Else
        OT = 0
      End If
    End If

    if NOT isnull (HomeDeptStop2) and isnull (TempDeptStop2) then
      if HomeDeptStop2 > TempDeptStop1 then
        if PreviousRunningTotal + HoursWorked > 40 then
          if 40 – PreviousRunningTotal < 0 then
            OT = HoursWorked
          Else
            OT = 40 – PreviousRunningTotal
          End If
        Else
          OT = 0
        End If
      Else
        OT = 0
      End If
    End If

    if isnull (HomeDeptStop2) and NOT isnull (TempDeptStop2) then
      if HomeDeptStop1 > TempDeptStop2 then
        if PreviousRunningTotal + HoursWorked > 40 then
          if 40 – PreviousRunningTotal < 0 then
            OT = HoursWorked
          Else
            OT = 40 – PreviousRunningTotal
          End If
        Else
          OT = 0
        End If
      Else
        OT = 0
      End If
    End If

  Else
    OT = 0
  End if
End if

In order to check multiple Temp Depts, I'm thinking I need to somehow loop through a recordset of the TempDept table looking at dates and stop times but I'm not really good with recordsets. After coming up with the above my brain is fried, I'm getting a cold, and I can't figure it out.  

Can some kind soul help me?

Debbie

 

[VBslammer]

It's hard to tell how your form is constructed without seeing it, but it appears that you have an [Hours] field on each of the subforms. I find that totaling fields on a subform is easiest to do by exposing the "Form Header/Footer" area in design view, then placing textboxes in the footer area to sum the fields with. For example, if the subform has a field for [Hours], the subform looks like this:

  Dept      Date     Start   Stop     Hours
================================================
+--------+--------+--------+--------+--------+
+--------+--------+--------+--------+--------+
+--------+--------+--------+--------+--------+
+--------+--------+--------+--------+--------+
================================================
          Footer Area               +--------+
                                    +--------+

The totaling textbox (called something like "txtTotalHours") has a simple formula like: [tt]=Sum([Hours])[/tt]

When you switch the subform to datasheet view, you can't see the footer area at all, but the main form can still reference its controls. So if you add a totaling textbox to each subform, all you have to do is add the 2 sums together and decide whether OT exists or not.

On the main form, add a textbox for OT called txtOT or something, and its formula would be like:

=IIf(subfrmHome.Form!txtTotalHours + subfrmTemp.Form!txtTotalHours > 40, subfrmHome.Form!txtTotalHours + subfrmTemp.Form!txtTotalHours - 40, 0)

VBSlammer

Unemployed in Houston, Texas

 

[willir]

Debbie

Ah payroll, my favourite! Can be one of the most complicated applications because there are some many exceptions.

First, would you consider changing your design?

An employee can work in more than one department.
A department have have more than one employee.
This is a many-to-many relationship which is usually tracked in a "joiner" or intermediary table.

It seems that you may be tracking hours in more than one table. And you have more than one Start / Stop times.

Consider...

tblHoursWorked
HourseWorkedID - primary table
Employeeid - foreign key to employee table
DeptCode - foreign key to department table
StartTimeDate - Date field
EndTimeDate - Date field

You can use the Time / Date fields to track both date and time. But if you prefer, you can use

tblHoursWorked
HourseWorkedID - primary table
Employeeid - foreign key to employee table
DeptCode - foreign key to department table
WorkDate - Date field
StartTime - Date field
EndTime - Date field

And use a more traditional approach of tracking work by capturing hours separate from the date.

Uising the first approach, data would like...
[tt]
tblHoursWorked
HourseWorkedID EmployeeId StartTimeDate EndTimeDate
1 1 01/02/05 08:00 01/02/05 12:00
2 1 01/02/05 13:00 01/02/05 17:00
3 1 01/02/05 18:30 01/02/05 21:30
4 1 01/03/05 07:30 01/02/05 13:00
[/tt]

Keep in mind that the only real apparent difference between your two tables is that one does not include the departmen code since it is assumed that the work occurred within the employee's defualt department, and the other table does include department code.

...Moving on
By using a more simplified apparoach, you can easily sum the hours worked for the week as a total or by department. This may be preferred over looking at Start / Stop 1 and 2. Not only do you just have to compare two fields, but you can actually have more than two work periods for the day, and multiple departments.

Now, are you trying to decide how to charge each department for OT, then you can look at working out percentages.

It seems that by including multiple start / stops, calculations become more complicated.

...Don't want to change your design?
Consider calculating hours worked for a department and loop through departments worked. Using peudo code...

RegHrs = 0
OTHrs = 0
For Deparements Employee Worked for during pay period
   HoursWorked = CalcHoursWorked(EmployeeID, DeptCode)
   If RegHrs less than or equal to 40
      If (RegHrs + HoursWorked) less than or equal to 40
          RegHrs = HoursWorked
      Else
          OTHrs = RegHrs + HoursWorked - 40
          RegHrs = 40
      End if
   Else
      OTHrs = HoursWorked
   End If
Next Deparment

Does this make sense to you?

Richard

 

[debbieg]

VBSlammer and Richard,

I appreciate your quick response.

Here are how my tables are set up:

Employee
* EmpNo
LastName
FirstName, etc.

DeptPay
* EmpNo
* DateEffective
HomeDept
ShiftCode
HourlyPayRate

HomeWorked
* EmpNo
* WorkDate
Start1
Stop1
Start2
Stop2

OtherWorked
* EmpNo
* WorkDate
* TempDept
Start1
Stop1
Start2
Stop2

The ultimate result is to create a CSV file every 2 weeks to be uploaded into ADP PC/Payroll system. I have to be able to capture if the OT hours are for the Home Dept or for the Other Dept so that that time and dollars can be charged to the appropriate Dept.

I wasn't going to have start and stop times at first but then I couldn't figure out how if I reached OT hours during a day with multiple entries if the OT hours were for the Home Dept or the Other Dept. I hope this is making sense. I'm almost afraid that I'm over my head on this one. I hate to admit defeat but I just can't figure this one out.

Thanks,
Debbie

 

[debbieg]

I should have added the OT column in my first post to make my task clearer:

(on a subform)
Home Dept:  555
[b]
Date        Start1    Stop1     Start2    Stop2    Hours  OT
[/b]
01/17/2005  08:00 AM  12:00 PM                        4
01/18/2005  01:00 PM  05:00 PM  05:30 PM  07:30 PM    6
01/19/2005  08:00 AM  12:00 PM  01:00 PM  05:00 PM    8
01/20/2005  08:00 AM  12:00 PM                        4
[COLOR=red]01/21/2005  01:00 PM  05:00 PM                        4    4[/color]

(on a subform)
Temp Departments 
[b]
Dpt Date       Start1    Stop1     Start2    Stop2    Hours  OT 
[/b]
512 01/17/2005 01:00 PM  05:00 PM  05:30 PM  07:30 PM    6
512 01/18/2005 08:00 AM  12:00 PM                        4
512 01/20/2005 01:00 PM  05:00 PM  05:30 PM  07:30 PM    6
[COLOR=red]550 01/21/2005 08:00 AM  12:00 PM                        4    2
512 01/21/2005 05:30 PM  07:30 PM                        2    2[/color]

There is a total of 48 hours with 8 hours of OT assigned to the appropriate department.

Hope that shows you what I have to calculate.

Debbie

 

[VBslammer]

Your tables should reflect the business model, but you should try to get them into 3rd normal form if possible.

[tt]Employee (Main Table)
PK EmpNo
LastName
FirstName, etc.
HireDate
FK HomeDeptID
FK ShiftID
FK PayTypeID
PayRate

Shifts (Lookup Table)
PK ShiftID
Description
StartTime
StopTime

PayTypes (Lookup Table)
PK PayTypeID
Description
PayPeriods

Depts (Lookup Table)
PK DeptID
DeptName, etc.

HoursWorked (Data Table)
PK HoursID AutoNumber
FK EmpNo
FK DeptID
WorkDate
ClockIn
ClockOut
TotalHours
[/tt]

VBSlammer

Unemployed in Houston, Texas

 

[willir]

VBSlammer and I are in agreement on this. His table HoursWorked is similar in design to mine (although I would think twice about including total hours worked since -- 3rd normal form vs practicality)

By just using one table to track your hours instead of hours working in the home vs non-home departments. This would simplify calculations, forms, reports, etc.

By tracking hours worked in two tables with two start / stop times will get messy.

Got most of your forms and reports done? I would still suggest that you revisit the design -- work done on re-ding froms will most likely pay-off later.

Do you have a lot of data entered already? Well, this would be more awkward -- moving data to a normalized is doable. The fact that you have two start/stop times would complicate things a bit but not overly so.

If you don't change your design, you may encounter...
- long and complicated queries
- lack of flexibility for future enhancments
- more time spent on maintenance

Richard

 

[VBslammer]

[blue]willir[/blue], as always - sound advice.

I sometimes add calculated fields to an otheriwse normalized table just to keep queries simpler. If someone is keying the hours in manually anyway, a calclulatd textbox can update that field when the data entry is performed, and queries can grab the total without doing any math - a big performance boost on large systems.

Also, in the case of timeclock systems, sometimes a supervisor has to be able to make adjustments to a hardware-supplied "punch" in or out, so a simple method is to adjust the hours worked.

The "normalized" way would be to add an adjustment table for adjustments to the punch times so you can see who made the adjustment, and how many times the entry was adjusted, while leaving the original entry intact; but at the cost of complexity.

The problem with staunchly normalized systems is that you need 10 JOINS just to find simple information, which is why I'd rather use SQL Server for all but the simplest databases, because it's much easier to work with stored procedures than mile-long queries.

[red]debbieg[/red],

Are you trying to capture OT per day, i.e. hours over 8 p/day?

One thing that may help if you want to keep your existing schema is to try UNION queries. They will allow you to capture the data from 2 different tables as long as each query retreives the fields in the same ordinal positions and field types.

Personally, I wouldn't try to put so much summary data on a data-entry form. I'd create reports to handle that so I could use GROUP BY clauses to show totals by Department, etc.

VBSlammer

Unemployed in Houston, Texas

 

[willir]

VBSlammer

Agreed on your "common sense". Hopefully, my rambling pointed this very fact out. Darn this or darn that. If I had of used a date field and start/stop CheckIn/CheckOut fields, somebody may have mentioned that the design was not 3rd normal; ditto for the total hours. However, your approach is just as valid. Hey, how about a compromise...

DateWorked
CheckIn
TotalHrsWorked

...where TotalHrsWorked is calculated when the end user enters the CheckOut time in an unbound field. ;-)

Richard