Scenario: One Table containing Multiple Projects (field called Project) and each Project has project activities (Activity).
In my Table, I set up two fields: Project (name of the project) and the Project Activities (so each record will be defined as an activity).
Limitation: The column [Project] is redundant since the name of the project will appear for each project activity:
[Project] | [Activity] | [Status]
1 Identify Stakeholders | Project A |Completed
2 Assign Team Leader | Project A |In Progress
3 Define Scope | Project A |In Progress
4 Validate Scope | Project A |Not Started
5 Identify Stakeholders | Project B |Completed
6 Assign Team Leader | Project B |Completed
7 Define Scope | Project B |In Progress
8 Validate Scope | Project A |Not Started
*Note 8 records where each record is the project activity. Two Projects in this table.
Objective 1: Count the number of activities per Project and visually display the total number of project activities by Project. Using the [Project] field there are 4 project activities for Project A and 4 project activities for Project B. Of course, when you Group the Grid View Table by Project, it shows a count but how can i visually display a summary of project in a Bar Chart or Pie Chart?
Objective 2: Calculate the percentage of completion of all activities based on status for each Project.
-Using example above, Project A Completion Rate is 25%, In Progress 50%, and Not Started 25%
-Project B is 50% completion rate (i.e., Records 5-6 completed)
I do understand that having everything in one table though makes it simple, perhaps a different approach may have been a one-to-many tables but I am not familiar with Roll-ups and Link Tables so if that is alternate approach, just let me know how to approach it would be appreciated.
But ideally, any thoughts on how to answer the objectives based on a Table showing project activities for multiple projects would be greatly helpful.
Thanks in advance for posting your question! When someone provides an answer, remember to mark their reply as the solution.