Discussions

We have a Work Order table to track services performed. Each work order can have up to 3 services.  Each service can have 2 employees who work on it, so there can be up to 6 employee and employee hour worked fields per work order.  An employee can work on more than one service per work order so they could show up up to 3 times per work order.  We would like to calculate how many billable hours an employee has per pay period from all work orders during that time.  What is the best way to capture this?

By far the best way is to have a child table of services performed.

One Work Order Has Many Services Performed.  The data entry can be by grid edit.

That will allow full flexibility on calculating employee hours. It also allows for the granularity  that not all the a services performed on a work order may not all be on the same day, and hence may cross a pay period.  if the hours are entered for any given date, then that will not be a problem.

Unfortunately, I don't think I provided enough information. There is a parent table Locations that has a child table Services. Each Location can have many Services. The Work Order table is a child of Services. Each Service can have many Work Orders.The Work Order table is also a child of the Employees table. Each Employee can have many Work Orders. In order to accomodate the 6 potential fields I created 6 relationships (is there a better way?). Therefore, in order to calculate Employee hours, the only way I can figure out how to do that is to run 6 different reports (1 per field/relationship on the Work Order).  That is my dilemma.  Is there a better way for me to set up the Employee-Work Order relationship(s)? Or is there a way to add the 6 fields together for reporting?

It's the 6 relationships that will kill you for reporting.

My suggestion is to have a new table of employee work order assignments which will be a child of employees and a child of work orders.  The employee time will be logged there.  The kill those 6 relationships or phase them out if you already have data.