Any idea on how to calculate project progress

[xaxier]

Hi all,

I had created two table, one is to track the user's progress and the other one is to track the project progress. The user will update the task based on the task assign and each task belong to certain project. Every time they completed a task, they will need to update this ticking "Yes" on the "Done" column and specify the date of completion. My project tracking is based on the number of completed task compare to the over all number task. Do you have any idea on how to let the user update the task and the project table will calculate and update the % of project completion automatically without having me to update this manually each time? Attached is the example of the table's involved

 

[xaxier]

Hi guys,

Just want to know if this is doable, Really need some guidance here. Thanks in advance!!

 

[DCrake]

You would need to know the total number of tasks upfront in order to calculate the %. Are you aware at point of entry?

 

[xaxier]

You would need to know the total number of tasks upfront in order to calculate the %. Are you aware at point of entry?

Hi there,

Thanks for your input. The number of task is actually based on the number of tasks for a particular project in Task:table, for example, if you look at the given attachment, in Task: table, you have Mitsudo project which has 5 tasks under it and out of the 5 tasks, users had indicated that they completed 3 out of it under the column Done. With this in mind, I would like the Project_progress: table able to update the Mitsudo project to 60% automatically ( I think I put it wrongly in that sheet at 80%). Can this be actually achieve?

 

[xaxier]

Hmm, is this doable? Do tell me if you guys have any idea on this.

Thanks in advance

 

[spoole]

Yes it is,

OK some basics,

lets say your table has 3 columns,

ProjectID, taskID and complete. Ok, so one project may have many tasks soem of which are complete and there may be may be many projects.

In order to work out the percentage of tasks complete first you need a query that will determine how many tasks there are for the project and then a query to find out how tasks are complete for that project.

So query number 1 is.

Add all 3 fields to your query in the order above. Then hit the "Totals" button on the the tool bar. Now group by ProjectID and select "count" from the drop down list for both the taskID and the complete fields, and output to a table. This table now has one row for each projectID and a count of the number of tasks in the project.

So query number 2 is.

Copy query number 1 and rename to whatever naming convention you use. Now open up the new query and in the complete column select only records where complete = 'YES' (or wahtever value you use to show complete) output to a new table and this table will contain a single row for each projectID and the number of tasks complete.

Query 3.

Open a new query and add both tables you created above and join the two tabels together by 'ProjectID'.

Add column 'ProjectID' from table 1 to the query. Now create a field that has this calculation. (taskid/taskid)*100

That means divide the taskid count in table 2 by the taskid count in table 1 then times it by 100 therefore (10/50)*100 =20%



Does that make sense??