The Projects table is parent to TimeDetails table. The Resource table is also a parent to Time Details. However, Projects and Resources are not related.

When adding new TimeDetail records, we enter the related project, the related resource, the work date, and the number of hours worked.

What I need to calculate is as follows >>>

#1 - The total number of hours worked per resource per project.

#2 - The total number of hours worked per project by all resources on that project. This one I have already as a summary field in the Projects table.

#3 - Divide #1 into #2. This results in a percentage that each resource contributed to each project.

#4 - Multiply #3 by the net profit of the project. This results in a $ amount contribution to net profit for each resource for each project. The net profit numbers would most likely come from the Projects table since total revenue and total expenses are already summed up there.

#5 - Need a running tally of #4 for each resource.

#6 - Need a running tally of the total net profit for all the projects that each resource has worked on.

#7 - Divide #5 into #6 which results in a percentage SCORE for each resource that we will use to compare our resources.

To make matters worse, we have 5 years of Project, Resource, and TimeDetail data that I would like to apply these calculations to as well as future data.

I.know this is a lot, but any guidance would be appreciated as I'm having trouble wrapping my head around this one. Thanks

When adding new TimeDetail records, we enter the related project, the related resource, the work date, and the number of hours worked.

What I need to calculate is as follows >>>

#1 - The total number of hours worked per resource per project.

#2 - The total number of hours worked per project by all resources on that project. This one I have already as a summary field in the Projects table.

#3 - Divide #1 into #2. This results in a percentage that each resource contributed to each project.

#4 - Multiply #3 by the net profit of the project. This results in a $ amount contribution to net profit for each resource for each project. The net profit numbers would most likely come from the Projects table since total revenue and total expenses are already summed up there.

#5 - Need a running tally of #4 for each resource.

#6 - Need a running tally of the total net profit for all the projects that each resource has worked on.

#7 - Divide #5 into #6 which results in a percentage SCORE for each resource that we will use to compare our resources.

To make matters worse, we have 5 years of Project, Resource, and TimeDetail data that I would like to apply these calculations to as well as future data.

I.know this is a lot, but any guidance would be appreciated as I'm having trouble wrapping my head around this one. Thanks