Calculate Hours Query

[Guest]

Hi,

I've got quite a dilemma on my hands. We use a staff database to monitor
all staff issues. When it was designed it included a section on Sickness
monitoring. However this section is based on:

Absence Number (Primary)
Staff Number (Foreign Key)
Leave Date
Return Date
Hours Missed

This structure was perfectly adequate at the time, however the organisations
needs have evolved over the past two years and now we need to be able to
calculate the amount of hours any given absentee has to date.

Currently if a member of staff goes off sick an absence form is completed
and the leave date entered. When they return we enter a return date and
calculate the hours missed manually.

It is done this way because the related staff table only stores the number
of hours an employee works per week. It does not record which days. What I
need to do is run a query to calculate how many hours any current absent
staff member has been away for.

I'm not sure if the structure will support this without including which days
staff work and this would be to much work as they are constantly changing
days. The only other suggestion I had was to include a field stating how
days these hours are worked over.

Can somebody please either confirm my beliefs or give me some suggestions.

Many thanks

Ian

 

[Allen Browne]

Ian, for your database to be able to calculate the number of hours missed,
it would need to have the worker's roster, i.e. which days they were
scheduled on, and for how many hours on each of those days.

Since it does not seem to have that data, it cannot calculate how many hours
were missed between 2 dates.

 

[Guest]

Hi,

I was affraid you might say that. I was thinking if I added a field to the
related staff table stating the number of days the employee worked the hours
over. I could use this field to get an estimate.

For example if I run a query to calculate the number of days between the
leave date and the current date then divide this by the number of days an
employee works over I wuold get a crude figure for how many days they've
missed to date. Then multiplying this figure by the number of hours worked
per day. Although not completely accurate would you agree its the best I can
do under the circumstanes?

Ian

 

[Allen Browne]

Up to you, Ian, but I suspect that kind of estimate would be crude enough to
create more problems than it solves.

If you have a situation where most people work Mon - Fri, you could use the
Workday math in this link:
http://www.mvps.org/access/datetime/date0012.htm