help! scheduling employees projects

[lisa[nospam].xxx]

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

[Salad]

lisa[nospam].xxx wrote:

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

Have you considered MS Project? It is a program designed to manage
projects and manpower and time frames. If you are a project manager, I
don't see how you could function w/o a tool like it.

[John Winterbottom]

"lisa[nospam].xxx" <"lisa[nospam].xxx"@libero.it> wrote in message
news:c6**********@newsfeed.cineca.it...

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:

These types of designs are best done using a calendar table. The calendar
table holds all the individual date elements, (days, weeks, months or
whatever) that you are ever likely to need. When you assign a project to a
person you specify the start and end dates for the project. Whether you use
months or days as your date unit depends on your needs - here I've used
days). These are the tables I suggest:

create table calendar
(
calDate datetime not null,
monthText varchar(15) not null,
yearText int not null,
constraint PK_calendar primary key
(calDate)
)

create table employees
(
empID int not null
constraint PK_employees primary key,
empName varchar(100) not null
)

create table projects
(
projectID int not null
constraint PK_projects primary key,
projectDescription varchar(255) null
)

create table assignedProjects
(
projectID int not null
constraint FK_assignedProjects_projectID
references projects(projectID),
empID int not null
constraint assignedProjects_empID
references employees(empID),
startDate datetime not null
constraint FK_projects_startDate
references calendar(calDate),
endDate datetime not null
constraint FK_projects_endDate
references calendar(calDate),
constraint PK_assignedProjects primary key (projectID, empID)
)
you can fill the calendar table with code like this

Private Sub fillCalendar()

Dim d As Date
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "calendar", CurrentProject.Connection, adOpenStatic,
adLockOptimistic, adCmdTable
For d = #1/1/2004# To #12/31/2009#
rst.AddNew
rst("calDate").Value = d
rst("monthText").Value = Format$(d, "mmm")
rst("yearText").Value = Year(d)
rst.Update
Next d
rst.Close
Set rst = Nothing

End Sub

[lisa[nospam].xxx]

John Winterbottom wrote:

These types of designs are best done using a calendar table. The calendar
table holds all the individual date elements, (days, weeks, months or
whatever) that you are ever likely to need. When you assign a project to a
person you specify the start and end dates for the project. Whether you use
months or days as your date unit depends on your needs - here I've used
days). These are the tables I suggest:
...snip...

Thank you very much John, but I really cannot make out how to use this calendar
table for my purposes. Can you help me?

Lisa