Hello all! Been struggling with this for a while with my current base. Subject of this post is a decent summary, but for explanation sake I've drastically simplified my base design:
Link to the simplified base I'm describing
tl;dr: The end goal is to look at a project, and total the number of hours spent on each task over the multiple scheduled events related to that project.
There are three Employees:
Employee A
Employee B
Employee C
There are three Tasks:
Task A
Task B
Task C
There are three Projects:
Project A
Project B
Project C
There are Scheduled Events.
Each Scheduled Event assigns an Employee to a single Project, and various Tasks for that project. For instance... Employee A is scheduled on Jan 1 to work on Project B. They are assigned to tackle Task A and Task C. This is what I'm calling a scheduled event.
Each day will have multiple scheduled events. Any combination of Employee, Project, or Task is possible for each day. Each employee might work on multiple projects in one day, and there may be more than one employee working on the same project in the same day. If that's not clear, this might be an example of a couple days
JAN 1:
Employee A - Project B - (Task A & Task C)
Employee B - Project B - (Task B)
JAN 2:
Employee A - Project C - (Task B)
Employee A - Project B - (Task A & Task C)
Employee C - Project A - (Task A, Task B, & Task C)
Up until this point, my base layout makes sense to me, and I can accomplish everything above successfully. The problem comes in logging and totaling specific Task Hours as related to the Project:
I need each employee to log their hours spent on each assigned task for each scheduled event. So in my previous example, on the Jan 1 event. Employee A would need to manually log hours for Tasks A & Task C.
The end goal is to look at a project, and total the number of hours spent on each task over the multiple scheduled events related to that project.
So the desired output would be something like:
Project A:
Task A: 25hrs
Task B: 2hrs
Task C: 14hrs
Project B:
Task A: 12hrs
Task B: 3hrs
Task C: 20hrs
... and so on
Do any of you have thoughts on the best way to add to/structure my base for this? I'd prefer to keep the basic structure I've described and shown in my simplified base, but very willing to adjust things to help me achieve this goal. In a perfect world, hours are logged on the event, and summed hours are displayed on the project table. Would appreciate any input anyone has to give! Sorry for the novel, I really did try to keep this as short as possible. Happy New Year, ya'll!
