Help with General Approach

  • Thread starter Thread starter Alan Nicoll
  • Start date Start date
A

Alan Nicoll

I don't know if anyone can help me with this, but...

I need to calculate daily and weekly overtime on temporary
employees from a number of different employment agencies,
each charging different rates and typically multiple
rates. For example, one agency wants daily overtime to
start at 8 hours per day for some employees, 12 hours per
day for others (based on job title), and weekly overtime
for hours over 40 per week. The employees also get
different hourly rates depending on job title and
department worked. Other agencies have all employees
getting daily overtime at 12 hours per day.

What is the general approach I should use for this? I
can't seem to get past square one in building queries even
to determine the overtime hours correctly. It's easy
enough to do this kind of thing in Visual Basic, but I'd
prefer to avoid that if possible because I won't be doing
this forever and others will come after me.

Thanks for any suggestions you can offer.

Alan
 
Hi,



Clearly there is two tables, at least, one to get the information about the
kind of job and its rules, since this has to be known somehow, and it is not
directly related with the "time sheet".

overtimes ' table name
JobID, FromThis, ToThat, PayFactorIs ' fields name

1010, 0, 37.5, 1.00
1010, 37.5, 50, 0.50
1010, 50 60, 0.50 ' data


Note that under PayFactorIs, we have what is "in addition" to the previous
steps.
so, for job 1010, normal time is from 0 to 37.5; extra 50% from 37.5 to 50
and another extra 50%, so, in total, double pay from 50 to 60.


timeSheet
EmployeeID, JobID, totalApprovedTime ' fields name


Here the design may differ, mainly if an employeeID can make different
JobID, and we probably need a WeekID, but for illustration, I prefer to
avoid details out of focus.



SELECT a.EmployeeID
SUM((a.totalApprovedTime-b.FromThis)*b.PayFactorIS) As

FROM overtimes As b INNER JOIN timeSheet As a
ON a.totalApprovedTime<=b.PayFactorIs

GROUP BY a.EmployeeID


should do. As example, if the totalApprovedTime is 57 hours, the inner
join, just before the aggregation/grouping, is:


a.totalApprovedTime b.FromThis b.PayFactorIs
57 0 1.00
57 37.5 0.50
57 50. 0.50



and the SUM will do:

(57-0)*1.00 + (57.37.5)*0.5 + (57-50) * 0.50 = 70.25


if we do it manually: 57 is 37.5h *1.00
+ 12.5h * 1.50 ' we are up
to 50 h
+ 7h * 2 'double pay for 50
to 57h
= 70.25


both methods supply the same result.


So, in summary, two points: having two tables (that, you probably already
know), and, to simplify the computations, get the "PayFactor" as the
"increase" in relation to the previous step. Again, from the range 37.5-50
to the range 50-60, we got from one and one half pay, to double pay, so, the
increase if half, 0.50, a pay. Sure, that for keeping the computations
simple. If people making "maintenance" of this data find that the actual
method is "hard", unnatural, prone to error, you probably have to build an
interface (Form) between what they "feel right and convenient" and the data
really stored in your "table" (which can be a query from another table
too)... but that is the whole fun of being a designer, I won't take that fun
from you.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


Well, now I compare oranges with apples. The JOIN should have been, I hope
you found it all by yourself:

ON a.totalApprovedTime>=b.FromThis

instead of

ON a.totalApprovedTime<=b.PayFactorIs 'which compare a time with a % ... :-(



Vanderghast, Access MVP
 
Back
Top