I’m trying to put together a time tracking system. Primarily need help with rollup summary tables. Probably one time situation, although there could be follow on work at some future point.
Here are the current tables:
-
Projects table: allocated time per Month and per Week per Project (we get a contract for project and enter the Hours allocated per Month and Week, contains Project Start Date and End Date)
-
Tasks table: manual task entry with Date, Actual Hours worked
-
Project Hours Logged table: manual daily time entries of hours against projects
Work Needed:
-
Project Hours Logged table: would like to replace manual entry with rollup of Hours per Day and Week from Tasks
-
Summary Hours table: rollup of Project Hours Logged by Week and by Month per Project to show what our utilization of Hours used compared to what Hours were allocated within Projects table (by Week and by Month)
Example:
Projects table contains:
Proj1 (name of project)
20 Hours allocated per Month
5 Hours allocated per Week
Tasks table contains task entries:
graphic design 2 hours May 6, 2019 (Proj1)
web design 10 hours May 6, 2019 (Proj1)
web design 1 hours May 15, 2019 (Proj1)
web design 8 hours May 22, 2019 (Proj1)
Project Hours Logged table should contain rollups:
Summary by Weeks:
Proj1 May 2019 Week#1 12 Hours
Proj1 May 2019 Week#2 15 Hours
Proj1 May 2019 Week#3 8 Hours
Proj1 May 2019 Week#4 0 Hours
Summary by Months:
Proj1 May 2019 21 Hours Worked, 20 Hours Allocated
Proj1 June 2019 0 Hours Worked, 20 Hours Allocated
