How to calculate the number of hours worked in a week period?

  • Thread starter Thread starter Abby B via AccessMonster.com
  • Start date Start date
A

Abby B via AccessMonster.com

Hi folks,

Here is my problem. I want to do some validation in an overtime entry field depending on the total number of regular hours worked in a week in another field?

I have a form that has both fields and I want to prevent the user from entring any value in the overtime field if he didn't exceed the regular hours worked per week. How can I get the totals per week (the week runs Sun to Sat)

Any thoughts.
Thanks in advance,
 
the table has the following fields:
time ID
regular hours
overtime hours
notes
 
Do you save the week # on the time entry. This value can be calculated or
for faster processing, can be added to a field in your table when the time is
entered. The formula for determining the week is:
DatePart("ww",[WorkDate])

What about not having an employee enter their own overtime? Why not just
have your database calculate the overtime hours based on the total hours
entered? You can accomplish this by adding a field for hours per week to
your employee table and all time would be entered in the same field but on
reports, payroll calc, etc the excess over the total per employee per week
would be put into overtime.

Just a thought. Otherwise, you could have the field visibility on your form
based on the total hours. Something like this on the After Update property
of your timeand on the After Update of your employee drop down (if you have
one):
If Me.TotalHours >= 40 then
Me.Overtime.Visible = True
Else
Me.Overtime.Visible = False


Hope this helps.
 
Thank you so much that really helps! But how will I get the total hours/week in the form after they enter their regular hours? Could this be a dsum function?
 
First I created a query that looked at all the time entry and did a sum on
only the records with matching employeeID, week and year. You can build the
query visually but the for clarification the SQL is:

SELECT Sum(tblTimeCard.HoursWorked) AS WeekHours, tblTimeCard.EmpID,
tblTimeCard.Week
FROM tblTimeCard
WHERE (((Year([Date]))=Year([Forms]![frmTimeEntry]![EntryDate])))
GROUP BY tblTimeCard.EmpID, tblTimeCard.Week
HAVING (((tblTimeCard.EmpID)=[Forms]![frmTimeEntry]![Employee]) AND
((tblTimeCard.Week)=DatePart("ww",[Forms]![frmTimeEntry]![EntryDate])));

Then, on my Time Entry form, I have an unbound field called WeekTotal. On
the After Update event of the field HoursWorked I have the following:

Me.WeekTotal = DMax("WeekHours", "qryWeekTotal")

Hope this is not too confusing.
Jackie :o)
 
That is great I tried it and it works but it doesn't include the active record in the calculation! Can I save in the update event before it puts the value?
 
Back
Top