How can I find, when overtime starts

[Annelie]

The user inputs on a form hours worked per day, but an employee can have
worked for several jobs so there are several line items per employee.
My fields are:
JobNo, TypeOfPay, MonHrs, TuesHrs, WedHrs, ThuHrs, FriHrs, SatHrs, SunHrs.

I can create subtotals for the days, and when it reaches 40, everything
above is Overtime.
The problems is with having several lines per day, lets say:
job 1830 mon 8, tue 8, wed 4 = so now I am at 20 hrs
job 1845 Wed 4, Thu 10, Fri 10 = now somehow I need to start with the 20 hrs
from the previous line.

Is it possible? Any thoughts would be appreciated.
Annelie

 

[Duane Hookom]

Is there any chance of normalizing your tables to get rid of the repeating
days of the week fields?

 

[Annelie]

The days of the week are in one row to make it easy to input the data. I
kind of copied that from Quickbooks "enter time" and it is an easy way to
input the data: Job No tab, day1 hrs tab, nextday hrs tab etc. , Job 2, day
hrs tab, nextday hors tab etc.
I am open to suggestion, may be this is not the easiest way.
Annelie
PS: I thank you from the bottom of my heart for being in the access
newsgroups, as well as the other MVPs. You have given me so much help, not
only by replying to my posts, but by answering so many posts and so many
times, I do not have to ask a question, I scroll for other peoples
questions, and my answer is right there. It is people like you, who give us
beginners a chance. You can't get this out of books.
Thanks you so very much,
Annelie

 

[Duane Hookom]

I don't generally base my table structure on what I want my screens to look
like. With screens like this, I generally use unbound forms that get filled
with values from a recordset and then written back to a recordset. I have
the feeling that unbound forms might be a little over your head for building
and maintaining (forgive me if I am wrong).

I don't quite understand what you expect to do with the twenty hours from
Job 1830 and what you expect to do with the overtime. Do you want to just
calculate the total hours for all jobs for the employee for the week? I
don't see any type of "WeekBeginning" field in your earlier post.

 

[Annelie]

You are probably right with the unbound forms.
For the date, I have a table, which has only 2 fields, date and default with
I connect with a query to the table.
Each new week gets started updating the prior weeks data to the year to date
table, deleting the current week table and by entering the new WeekEnding
Date.

How would you have structured the table to get the result
job1 mon 8
job1 tue 8 = 16 hrs
job1 wed 8 = 24 hrs
job2 thu 8 = 32 hrs
job3 fri 6 = 38 hrs
job2 fri 4 = 42, 2 hrs at regular pay and 2 hrs at overtime pay.

I thought I could easily arrive at the accumlated totals with "if"
statements, but its all those jobs that are giving me the problem.
Whether I structure the table hours horizotally or vertically, I have to add
hours from all jobs which, I think, will always have come from several lines
items. The people that work in the carpentry shop, work on up to 5 jobs a
day.
Annelie

 

[Duane Hookom]

You can use a totals query to sum an employees hours across multiple records
for the same week. A normalized table structure would sum just one field.
Your structure would require summing six fields.

 

[Dale Fye]

Annelie,

Most of the time recording databases I have seen have a table with the
structure similiar to the following:

EmpID: LongInt or Autonumber
WorkDate: date
JobNumber: LongInt or String
HoursWorkedouble

Then, to get the total number of hours worked during a given period, and the
number of regular hours/overtime hours for a given week for each employee,
you can write SQL like:

SELECT EmpID,
WeeklyHours,
Minimum(WeeklyHours,40) AS RegHours,
Maximum(WeeklyHours-40,0) AS Overtime
FROM (
SELECT Tbl_EmpHours.EmpID,
Sum(Tbl_EmpHours.HoursWorked) AS WeeklyHours
FROM Tbl_EmpHours
WHERE Tbl_EmpHours.WorkDate>=cdate(SomeDate)
AND Tbl_EmpHours.WorkDate<cdate(SomeDate)+7
GROUP BY Tbl_EmpHours.EmpID) AS T;

To use this, you have to create the Minimum and Maximum functions, which are
different than the MIN and MAX aggregate functions. Minimum and Maximum
will return the minimum or maximum values from a variant array passed to the
function. I use these whenever I want to compare two or more numeric or
date values.

Public Function Minimum(Paramarray varArray()) as variant

Dim intLoop as Integer
Minimum = NULL
For intLoop = LBOUND(varArray) to UBOUND(varArray)
if ISNULL(Minimum) OR varArray(intLoop) < Minimum then
Minimum = varArray(intLoop)
endif
next intLoop

end Function

Public Function Maximum(Paramarray varArray()) as variant

Dim intLoop as Integer
Maximum = NULL
For intLoop = LBOUND(varArray) to UBOUND(varArray)
if ISNULL(Maximum) OR varArray(intLoop) > Maximum then
Maximum = varArray(intLoop)
endif
next intLoop

end Function

HTH
Dale

 

[Annelie]

Hi Dale,
I will give that a try, I just don't see where you are looking at the hours
in total, as well as the job?
Annelie

Annelie,

Most of the time recording databases I have seen have a table with the
structure similiar to the following:

EmpID: LongInt or Autonumber
WorkDate: date
JobNumber: LongInt or String
HoursWorkedouble

Then, to get the total number of hours worked during a given period, and the
number of regular hours/overtime hours for a given week for each employee,
you can write SQL like:

SELECT EmpID,
WeeklyHours,
Minimum(WeeklyHours,40) AS RegHours,
Maximum(WeeklyHours-40,0) AS Overtime
FROM (
SELECT Tbl_EmpHours.EmpID,
Sum(Tbl_EmpHours.HoursWorked) AS WeeklyHours
FROM Tbl_EmpHours
WHERE Tbl_EmpHours.WorkDate>=cdate(SomeDate)
AND Tbl_EmpHours.WorkDate<cdate(SomeDate)+7
GROUP BY Tbl_EmpHours.EmpID) AS T;

To use this, you have to create the Minimum and Maximum functions, which are
different than the MIN and MAX aggregate functions. Minimum and Maximum
will return the minimum or maximum values from a variant array passed to the
function. I use these whenever I want to compare two or more numeric or
date values.

Public Function Minimum(Paramarray varArray()) as variant

Dim intLoop as Integer
Minimum = NULL
For intLoop = LBOUND(varArray) to UBOUND(varArray)
if ISNULL(Minimum) OR varArray(intLoop) < Minimum then
Minimum = varArray(intLoop)
endif
next intLoop

end Function

Public Function Maximum(Paramarray varArray()) as variant

Dim intLoop as Integer
Maximum = NULL
For intLoop = LBOUND(varArray) to UBOUND(varArray)
if ISNULL(Maximum) OR varArray(intLoop) > Maximum then
Maximum = varArray(intLoop)
endif
next intLoop

end Function

HTH
Dale

 

[Annelie]

How do I create the public function?
Annelie

Annelie,

Most of the time recording databases I have seen have a table with the
structure similiar to the following:

EmpID: LongInt or Autonumber
WorkDate: date
JobNumber: LongInt or String
HoursWorkedouble

Then, to get the total number of hours worked during a given period, and the
number of regular hours/overtime hours for a given week for each employee,
you can write SQL like:

SELECT EmpID,
WeeklyHours,
Minimum(WeeklyHours,40) AS RegHours,
Maximum(WeeklyHours-40,0) AS Overtime
FROM (
SELECT Tbl_EmpHours.EmpID,
Sum(Tbl_EmpHours.HoursWorked) AS WeeklyHours
FROM Tbl_EmpHours
WHERE Tbl_EmpHours.WorkDate>=cdate(SomeDate)
AND Tbl_EmpHours.WorkDate<cdate(SomeDate)+7
GROUP BY Tbl_EmpHours.EmpID) AS T;

To use this, you have to create the Minimum and Maximum functions, which are
different than the MIN and MAX aggregate functions. Minimum and Maximum
will return the minimum or maximum values from a variant array passed to the
function. I use these whenever I want to compare two or more numeric or
date values.

Public Function Minimum(Paramarray varArray()) as variant

Dim intLoop as Integer
Minimum = NULL
For intLoop = LBOUND(varArray) to UBOUND(varArray)
if ISNULL(Minimum) OR varArray(intLoop) < Minimum then
Minimum = varArray(intLoop)
endif
next intLoop

end Function

Public Function Maximum(Paramarray varArray()) as variant

Dim intLoop as Integer
Maximum = NULL
For intLoop = LBOUND(varArray) to UBOUND(varArray)
if ISNULL(Maximum) OR varArray(intLoop) > Maximum then
Maximum = varArray(intLoop)
endif
next intLoop

end Function

HTH
Dale

 

[Dale Fye]

Depends on what version of Access you are using. I assume you are using 2K
or XP, I've only got 97 installed on my home PC, so cannot give you the
specifics. I would normally put this in a code module rather than a forms
code module because I want to be able to use the function from anywhere in
my program.

I'll check back in from work tomorrow, where I can look at it and make sure
the steps are correct, but it would help to know what version you are using.

Dale

 

[Dale Fye]

Annelie,

The query I wrote below actually contains a subquery. You could break this
apart as follows.

qry1: This is basically everything that follows the first FROM in my
original post. What this does is sum the total number of hours worked by
each employee during a given one week period.

SELECT Tbl_EmpHours.EmpID,
Sum(Tbl_EmpHours.HoursWorked) AS WeeklyHours
FROM Tbl_EmpHours
WHERE Tbl_EmpHours.WorkDate>=cdate([SomeDate])
AND Tbl_EmpHours.WorkDate<cdate([SomeDate])+7
GROUP BY Tbl_EmpHours.EmpID

qry2: This query uses the first query as its source, and just breaks up the
WeeklyHours value into its two components (regular, and Overtime). It does
this by using my Minimum and Maximum functions. Minimum(WeeklyHours, 40)
will return the number of hours worked if the individual worked 40 or less
hours, and 40 if they worked over 40 hours. The Maximum(WeeklyHours-40, 0)
will return the number of overtime hours. If Weekly hours is less than 40,
the Maximum function will ensure that the value is 0.

SELECT EmpID,
WeeklyHours,
Minimum(WeeklyHours,40) AS RegHours,
Maximum(WeeklyHours-40,0) AS Overtime
FROM qry1

HTH
Dale

 

[Annelie]

Thanks for continuing with my problem.
Sorry, I did not get back the last 2 days, been going crazy finally doing my
own tax returns.
I am using Access 2003 in Access 2002-2003 format
I actually did create the minimum and maximum function as module in a copy
of my database, but it did give me an error access error message that it
could not find the "field" minimum or maximum. In the mean time I
accidentally deleted my tryout database and I will have to re-create the two
modules again, and I used my current data and by query created a table in
the format you specified. I'll do that again in the morning.
It would really be great if you stick with me on this one. It would be such
a nice solution.
I'll post back after my re-creation tomorrow.
Thanks,
Annelie