how do i calculate normal time and overtime

[Guest]

I have created my database and have nearly everything working but I need to
be able to calculate the hours completed by the empoyees as normal, time and
half and double time on a daily and weekly basis.

I want to create a report that shows normal, t 1/2 and double time as a
daily calculation then as a total at the bottom of the page.
[This is also needed for a job costing report, hence the daily figs.]

Is it going to be easier to somehow export the figs into a spreadsheet then
back to access as a report or do I need to create a query of some sort with
calulations.

Any help appreciated - I can see the light at the end of the tunnel - just
can't quite reach it.

 

[David S via AccessMonster.com]

Should be easy enough, so long as the table containing the hours worked by an
employee in a day can distinguish between hours worked under "normal", "time
and a half" and "double time" conditions. It can do so in one of two ways:

* Your data table can store each of these values as a separate field, so your
table would look like:

fldEmployee, DateWorked, NormalHours, OneHalfHours, DoubleHours

* Your data table can store the start and end time an employee worked and
another data table contains the start and end time to which one and a half
and double time apply:

The former approach is much easier to do - the second approach is much more
flexible, but harder to code / write the correct queries. What have you got
to start with?

 

[James A. Fortune]

I have created my database and have nearly everything working but I need to
be able to calculate the hours completed by the empoyees as normal, time and
half and double time on a daily and weekly basis.

I want to create a report that shows normal, t 1/2 and double time as a
daily calculation then as a total at the bottom of the page.
[This is also needed for a job costing report, hence the daily figs.]

Is it going to be easier to somehow export the figs into a spreadsheet then
back to access as a report or do I need to create a query of some sort with
calulations.

Any help appreciated - I can see the light at the end of the tunnel - just
can't quite reach it.

I will be addressing this problem during the next couple of weeks. The
basic rules for overtime at the site under consideration are:

1) Overtime hours will begin after 40 hours have been accumulated.

2) Doubletime hours will be paid Sundays only after 58 hours have been
accumulated.

The time cards for a particular week might not be in sequential order.
Particularly, some of the time tickets prior to Sunday may not be
available for the calculation of overtime and doubletime. Prior to
Sunday, prior time tickets may not be available for the calculation of
overtime.

The secretary would like to see the running total for the week,
including OT and DT, on the time ticket entry form.

Exceptions exist, such as certain employees getting credited with
overtime for all hours on a particular rush job.

The secretary belives that having Access automatically compute OT and DT
will save much time and improve accuracy.

I will post the code I use to solve this problem on this NG if it seems
useful. If so, I will also indicate how to deal with daily overtime.

James A. Fortune

 

[jimfortune]

I have created my database and have nearly everything working but I need to
be able to calculate the hours completed by the empoyees as normal, time and
half and double time on a daily and weekly basis.

I want to create a report that shows normal, t 1/2 and double time as a
daily calculation then as a total at the bottom of the page.
[This is also needed for a job costing report, hence the daily figs.]

Is it going to be easier to somehow export the figs into a spreadsheet then
back to access as a report or do I need to create a query of some sort with
calulations.

Any help appreciated - I can see the light at the end of the tunnel - just
can't quite reach it.

I haven't coded this yet, but here is my plan:

The relevant fields from tblTimeTickets are:

TimeTicketID Auto
Regular Dbl
OT Dbl
DT Dbl
ChargeDate Date
EmployeeID Long
Lock Y/N

The hours in my table are stored as 1/4 hour durations but can be
adjusted to use start and stop times.

All hours for each employee during the week are entered as Regular
hours unless they are for automatic overtime. In that case, the hours
are entered in the appropriate textbox and saved with Lock = True. At
the end of the week (or sooner if a summary is to be shown on the
form), a command button will process the allocation of overtime. Since
time tickets can be entered in any order, a recalculation involves
resetting the values. For resetting, any records in tblTimetickets
where Nz([Lock], False) <> True will have Regular, OT and DT summed,
put into Regular and Null put into OT and DT. After that the overtime
rules will be applied to the time tickets (including rules for daily
overtime) for each employee for the selected week sorted by date order
(note: multiple time tickets for the same date are possible) then by
maybe the PK such that some or all of the Regular hours are shifted to
the OT or DT fields. When a late time ticket comes in, the button can
be clicked again to redo the computations. When every time ticket has
been turned in for the week, the overtime is recalculated, then Lock is
set to True for all time tickets for the week.

It is interesting to note that if one wishes to know all the days a
particular employee worked, a query of tblTimeTickets will return those
dates.

I will try to post some of the code later.

James A. Fortune