Calculate Hours Query

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"NoviceIan" wrote in message
...
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

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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"NoviceIan" wrote in message
...
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"NoviceIan" wrote in message
...
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" wrote:

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.

"NoviceIan" wrote in message
...
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.