resource scheduling

[Mohan]

Hi
I have created a simple project tracking tool in MS Access. I already have
the tables setup to track the recources in the project.
I have the following table (among others)
- Project table (ProjectID PK)
- User tabel (UserID PK)
- Users_on_Project table (useronprojectID PK, ProjectID FK, UserID FK)

The new requirement is that the project manager (PM) will enter a) start
date b) end date c) Percentage of their time that will be used in the project
In addition, for each resource, PM will enter their vaction period, other
admin work as a percentage (i.e.: 10 % of the resource's time will be spent
on admin related to the project)
I need to produce a recource graph (or a grid) listing the follwoing on the
project viewing main screen (utilization of each resource on the project for
each month)
Resource Name Jan Feb Mar.. .... .. Nov Dec
Resource A 10% 15% 20% 0% 5%
Resource B 50% 5% 10% 10% 15%

(The above is for resources used in the project)

On a separate screen (or report) , I need to view the same info in a
different way:

Project 1 30% 20%
Project 2 10% 40%
Resource Name: Resource A Recource B

My first question is: if the PM enteres the start_date., end_dat and
perce_utilized, How can I translate that for each month?
i.e.: if start_date = 01/13/2007 end_date = 04/20/2007, percent_utilized =
15%
How can I figure out & display the percent_utilized for each month Jan = ?
Feb = ? March = ? April = ?

I am open to other suggestions (for data entring) or any other reference
would be helpfull

Thanks

 

[Stefan Hoffmann]

hi Mohan,

I have the following table (among others)
- Project table (ProjectID PK)
- User tabel (UserID PK)
- Users_on_Project table (useronprojectID PK, ProjectID FK, UserID FK)

The primary key should be a combined key from ProjectID and UserID,
otherwise you must create or use an unique index on both fields. This is
necessary for a M:N relationship here.

My first question is: if the PM enteres the start_date., end_dat and
perce_utilized, How can I translate that for each month?
i.e.: if start_date = 01/13/2007 end_date = 04/20/2007, percent_utilized =
15%
How can I figure out & display the percent_utilized for each month Jan = ?
Feb = ? March = ? April = ?

You must calculated it using the days per month over your period. Using
a simpler example:

start_date = 01/13/2007 -> 19 days / 31 days = 61%
end_date = 02/19/2007 -> 19 days / 28 days = 68%
percent_utilized = 15%

gives us
Jan = 15% * 61% = 9,2%
Feb = 15% * 68% = 10,2%

I would create a table to store the calculation results to present them
on a form or a report.


mfG
--> stefan <--