How to use calculations with a subset of records in a table
I'm attempting an application to track, among other things, work assignments of a company's employees. I am designing an Employee table which will have much of the typical employee info including the number of hours each employee works (both full and part-time employees). I plan on using summary fields to display number of employees, average salaries, etc.
I also plan on having a Work Assignment table which contains the assigments for each employee, using their EmployeeID from the Employee table. Employees generally will be assigned multiple assignments and each assignment will specify the number of hours allotted to the assignment and those hours could be inside a day's worth of hours or span several days. From this information I need to create a Layout which shows each employee and the % of time each employee is being utilized over a given period of time, say the next quarter for this particular Layout. Since I need to use a calculation against subsets of employee assignment records in the table I do not see how summary fields or sub-summary sections will help me.
Is there a way I can calculate each employee's % utilization and store it in the Work Assignment table? If so how would I do that, and if not what should I do? It would also be very helpful as I am making work assignments to have an employee's utilization % updated dynamically. How could that be accomplished?