Hours Weekly To Hours Monthly Problem

[Guest]

I maintain a database that tracks employee hours entered into time-sheets. In
essence, one column accumulates the number of hours entered daily. Another
column tells me the number of hours each employee is required to enter weekly
(for example, 40). I set up a couple of parameters (Start Date and End Date).
I can then easily collect the hours entered by an employee for a week and
compare that with his required weekly hours. An automated mail merge sends
emails notifying each employee who has missing hours.

My problem is this. I want to create a query that collects the entered hours
for a monthly fiscal closing. Months can vary. Sometimes they are three weeks
long, other times four, or even five. I want to enter the Start Date and End
Date for that monthly period and compare the total hours entered in the month
by each employee to what would be the total hours required of him for that
period. I can't figure how to sum up the required weekly hours (e.g., 40) to
the total relevant monthly hours that would be required. I don't know if I'm
clear enough on this. Can anyone help me?

 

[Guest]

This has to start with defining what your company's "required monthly hours"
are. Presumably it's the number of work days multiplied by 8. And since
you've already accomplished this for individual weeks, you're already solved
the issue of what is a "workday" (in view of holidays etc.) E.G. that your
work week is always 40 hours and you credit hours for holidays etc. , or that
"workday" is an entry in your DB system.

So "required monthly hours" is just # of workdays in the month multiplied by
8 (???)

 

[Guest]

Actually, no. There is an entry made at the time the employee is set up that
states the required weekly hours as 40. Some employees have a requirement of
37 hours, others 30, still others 25, etc. Also, as I meant to make clear in
my question, each fiscal month can vary from three, to four, to five weeks.
I'm picking up the number of hours entered by each employee alright, and I
can compare their weekly hours entered to their weekly required hours easily,
too. But, when I enter the Start Date and End Date parameters to select more
than one week, I can't figure what formula to use so that I show the
corresponding number of hours that would be required for the parameter dates
when the selection contains more than one week. Is this clearer?

 

[John W. Vinson]

Actually, no. There is an entry made at the time the employee is set up that
states the required weekly hours as 40. Some employees have a requirement of
37 hours, others 30, still others 25, etc. Also, as I meant to make clear in
my question, each fiscal month can vary from three, to four, to five weeks.
I'm picking up the number of hours entered by each employee alright, and I
can compare their weekly hours entered to their weekly required hours easily,
too. But, when I enter the Start Date and End Date parameters to select more
than one week, I can't figure what formula to use so that I show the
corresponding number of hours that would be required for the parameter dates
when the selection contains more than one week. Is this clearer?

I see this as being two separate, almost unrelated calculations. A week will
typically span more than one month, after all! You can calculate the number of
working hours during a week, and then - separately - the number of working
hours during a month. How do you want to calculate the monthly report, if the
month starts on a Friday? Do you need to calculate the hours worked Monday
thru Thursday during the previous month and count it toward that month, or
pro-rate the one day, or what???

John W. Vinson [MVP]

 

[David Portwood]

My first thought is that you are comparing apples and oranges. You talk
about having a column with hours entered daily and also a column with weekly
required hours. I can see where a query might have difficulty dealing with
this. Instead of a column specifying weekly required hours, how about a
column specifying daily required hours? Then you would have a column with
daily actual hours and a column with daily required hours. Seems like this
might be easier to work with.

 

[Guest]

I'm working with databases that are already created so I don't have much
leeway to change the structure of tables already constructed. For whatever
reason, the original has that pesky column of required hours per week, while
the time-sheets maintain hours worked daily. I actually don't care much about
when the work week begins. What is important to me is when the financial
month closes. It always closes on Friday. So, the next financial period
begins the immediately following day - a Saturday, although our work week
really starts on Monday. Some people on call may work Saturdays and Sundays.
Yes, I see that the calculations are unrelated. The weekly calculations are
easy. I just have parameters for the Start Date and End Date: you type those
in. Then, I select the hours logged by each employee in their time-sheet from
the date greater than or equal to the Start Date and less than or equal to
the End Date. I summarize those hours grouped by employee for that period
(typically a week) and compare that sum to the required hours. If the sum is
less than the required hours, then the difference is 'missing' hours. I send
out weekly notices via a mail merge to all employees with missing h ours. As
I said, that's the easy part.

Now, again realizing that the tables give me the hours required from an
employee per week, I'm trying to set up a calculation formula that will
provide me the total results for a fiscal month. Each month will also have
parameter Start and End dates. What makes it difficult is that the logged
hours are recorded on a daily basis while the total hours in the 'required'
column remains the same every day, say 40. Simply summing them as I do with
the daily hours does not produce a result for the parameter period.

 

[Guest]

Quite perceptive. I tried to make that clear. I think you've got it. What you
are missing is that I cannot change the structure of the existing tables that
collect the data.

 

[David Portwood]

You can virtually change the structure of tables by creating calculated
fields in queries. You can then sum the calculated fields (usually in the
same query).

I hope these ideas help, but of course I don't know the details of what you
are doing.

 

[Guest]

Please don't post such general responses. They are not at all helpful. I
tried to make myself clearer in my reply to John Vinson above, but I'm afraid
your response has just made my request get lost in the listings. I know I can
create calculated fields in queries and that I can sum those in the same or
other queries. What I need is some help in fashioning some calculation that
will total the required hours for a fiscal period stated in start and end
date parameters.