Sorry, I can't think of a subject line!

[scubadiver]

I have a working hours database for staff and it records basic and overtime
hours on separate records. I also want to record enquiry volumes on a weekly
basis.

The purpose of this is to calculate productivity on an hourly and cost basis
and this would require aggregating the working hours records on a
week-by-week basis.

How do I then link up the aggregated data with the enquiry volumes by week.

thanks

 

[BruceM]

Do I take it to mean you calculate productivity based on the number of hours
worked, whether regular time or OT? Are the separate records in the same
table? By what means are the separate records associated with the same
person and the same block of time? In other words, there is a regular time
record and an OT record. What is the connection between the two records?
Not using separate records would have been simpler. Either enter the time
for a given day as regular time and OT, or enter a total number of hours and
have Access do some calculations. For instance, ten hours may be eight
regular hours and two OT hours.

 

[scubadiver]

thanks

The record is the weekly total for basic hours and overtime and productivity
is based on an aggregate of regular and overtime records each week

There are pros and cons to have separate records. It helps to calculate
total pay more accurately if the over-time rate changes but having the
different record types on the same line does help but for different reasons.

In my old database it was complicated and convoluted.

needs a bit of thinking then.

thanks.

 

[scubadiver]

I think I may have made it too difficult

Here are the three relevant tables:

tble_hrs
RecordID(PK)
EmployeeID (FK)
RecWk
RateID
Dept
CCentre
Subdept
Wage
HrsWrk

tble_absence
RecordID (FK)
Leavetype
LeaveNum

tble_week
DateID
RecWk(PK)
Period
Phol
ALSN_IN
ALSN_OUT

"tble_hrs" is the list of records
"tble_absence" are the list of absences associated with each record
"tble_week" contains week information and enquiry volume fields

As it stands, the database is ok but, in order to calculate the productivity

I have two complications. The first is that the total cost calculation for
temps is different to permanent staff and the second is that I am
differentiating between those who have paid holiday and those who don't
because I need to take off the cost of paid holiday to get the cost actual
hours worked... then I calculate productivity...

oh dear!