Splitting a record in two

[Alan Nicoll]

I am working on a database that includes overtime
calculations. Each record represents a shift worked by an
employee. The problem is that when overtime must be paid,
it often must be paid for only part of a shift.

For example, if an employee works 35 hours in his first
four shifts, and in his fifth shift he works 8 hours,
that's a total of 43 hours for the week. For various
reasons it is desirable to create a record, i.e., split
that fifth shift into two records, one to be paid at
straight time and one to be paid at overtime.

Can someone tell me how to do this without getting into
Visual Basic?

Thanks.

Alan

 

[Bill Taylor]

You could have two fields in your "Pay" table one for regular hrs and one
for OT. A query could be set to calculate ttl hrs. and an IIF statement in
the source fields for the regular and OT fields:
Regular Hours Field IIF (sum(MyTable.Time) > 40, 40, sum(MyTable.Time))
OT Hours Field IIF (sum(myTable.Time) > 40, sum(myTable.Time) -
40 )
HTH

 

[Alan Nicoll]

If I follow you, I could then select records for my report
depending on which field was non-zero, and in those shifts
where there was both OT and straight time, the record
would show up twice in the report. Or at any rate, that's
how I think I need to do it.

Thanks for the help. I'm a rank beginner at this and
knowing about IIF will help a lot.

Alan