D
David Kinsley
I am trying to create a database solution to record daily
time sheets and generate reports needed for payroll.
Specifically, a workers pay rate is determined by the
amount of hours they work in a given week. A worker gets
regular pay for up to 40 hours a week. From 40-55 hours
they get OT pay and any hours over 55 they get Double
Time (DT) pay. Another stipulation is that if a worker
gets called in after they leave (say for emergency) they
either get 4 hours of regular pay or OT pay (which ever
is greater). It basically works out to if they get
called in and work 0-2.6 hours they get 4 hours of
regular pay. If they work over 2.6 hours they get that
time at OT pay. Also just to make matters a little more
complex, they have to reach 40 hours before they can get
any credit for OT pay.
I set up a query where you can specificy a range of dates
(in this case a week) where it will look at the total
hours and put the appropriate hours worked in each
column. For example, the results would look like this:
Name Total Hrs Reg OT DT
emp 1 47 40 7 0
emp 2 60 40 15 5
emp 3 38 38 0 0
The way I built my query is by using if statments to
basically look at the total hours wokred and then
distribute it appropriately; however is is solely based
on a single 40 hour week. (I've included my if
statements to determine the regular hours portion at the
end of this).
Thus if I want to run the report for two weeks time, I
would get something like this:
Name Total Hrs Reg OT DT
emp 1 80 40 15 25
emp 2 88 40 15 33
etc etc
Is there any way to write an update query or any other
solution such that it will update the table each week I
run the query, so that this data becomes static instead
of dynamic. As it stands now the data is always dynamic,
but once a week goes by it become historical and thus
shouldn't change.
If anyone can help me I would be very grateful as I am
having a lot of trouble coming up with a solution.
TReg: IIf([Sum of Hrs]>39.99,40+[Sum of Regs],IIf([Sum of
Hrs]<39.99,IIf(([Sum of Hrs]+[Sum of Ot])<39.99,([Sum of
Hrs]+[Sum of Regs]+[Sum of Ot]),IIf(([Sum of Hrs]+[Sum of
Ot])>39.99,(40+[Sum of Regs]),"check me"))))
Sum of Hrs = total regular hours inputed
Sum of OT = total OT hours inputed they were called in
Sum of Regs = total regular hours inputed after they were
called in.
time sheets and generate reports needed for payroll.
Specifically, a workers pay rate is determined by the
amount of hours they work in a given week. A worker gets
regular pay for up to 40 hours a week. From 40-55 hours
they get OT pay and any hours over 55 they get Double
Time (DT) pay. Another stipulation is that if a worker
gets called in after they leave (say for emergency) they
either get 4 hours of regular pay or OT pay (which ever
is greater). It basically works out to if they get
called in and work 0-2.6 hours they get 4 hours of
regular pay. If they work over 2.6 hours they get that
time at OT pay. Also just to make matters a little more
complex, they have to reach 40 hours before they can get
any credit for OT pay.
I set up a query where you can specificy a range of dates
(in this case a week) where it will look at the total
hours and put the appropriate hours worked in each
column. For example, the results would look like this:
Name Total Hrs Reg OT DT
emp 1 47 40 7 0
emp 2 60 40 15 5
emp 3 38 38 0 0
The way I built my query is by using if statments to
basically look at the total hours wokred and then
distribute it appropriately; however is is solely based
on a single 40 hour week. (I've included my if
statements to determine the regular hours portion at the
end of this).
Thus if I want to run the report for two weeks time, I
would get something like this:
Name Total Hrs Reg OT DT
emp 1 80 40 15 25
emp 2 88 40 15 33
etc etc
Is there any way to write an update query or any other
solution such that it will update the table each week I
run the query, so that this data becomes static instead
of dynamic. As it stands now the data is always dynamic,
but once a week goes by it become historical and thus
shouldn't change.
If anyone can help me I would be very grateful as I am
having a lot of trouble coming up with a solution.
TReg: IIf([Sum of Hrs]>39.99,40+[Sum of Regs],IIf([Sum of
Hrs]<39.99,IIf(([Sum of Hrs]+[Sum of Ot])<39.99,([Sum of
Hrs]+[Sum of Regs]+[Sum of Ot]),IIf(([Sum of Hrs]+[Sum of
Ot])>39.99,(40+[Sum of Regs]),"check me"))))
Sum of Hrs = total regular hours inputed
Sum of OT = total OT hours inputed they were called in
Sum of Regs = total regular hours inputed after they were
called in.