Hello! I need help to organize the working time spent by each employee
on projects. I thought this wuold be a rather simple probelm, but i realized it is not!!
I have many projects going on, starting at different dates, and each
employee can work on 0/N projects, for # months.
I built three tables: Projects, Employees, Manmonths.
Projects: (K) project_id, acronym, start_date, end_date, ....
Employee: (K) empl_Id, full_name, ....
Manmonths: (K) project_id, (K) empl_id, manmonths
WHen I ask for a report, I would like to see something like this:
empl_id:
full name:
gen feb mar apr mag giu lug ago set ott nov dic
2002 PRG1 PRG1
2003 PRG2 PRG2 PRG2 PRG2 PRG2 PRG3 PRG3
2004 PRG3 PRG3 PRG3 PRG4 PRG4 PRG4 PRG4 PRG4
where PRGnr can be either the Project acronym or the project_id, no matter.
The insertion form only asks how many months it takes for the person to dedicate
to a certain project, no other information, since I suppose
he starts working as the project starts. This is the mistake!
I see the trouble when, for example, I have two projects, both
starting on 1st December 2002, I say that the man works 3 months in
the first project and 4 months in the second one. I should find a way
to schedule his activity in such a way that he's busy for the next 7 months.
When I ask how long did he work, in the interval 1st december 2002-1st
march 2002 I get 6 months out of 3!
I tried to figure out how to better manage the situation but I cannot
find a solution. Can anyone help me?
I'm not asking you to develop a fully functional scheduling application for me,
just help me in finding a way out of this...
Thank you in advance
Lisa