project time capacity

[david]

I have a database that among other things, I want to keep track of the
amount of time that each employee spends on projects. I have a field
for when the project was submitted and a field when the project is due.
There is a field on the estimated time that it will take to due this
project. From this I can calculate how much time per day that each
employee has going to their projects. I'm trying to find a way to
show when an employee is at their maximum capacity or if they can take
on another project. I am finding that I can do this for what is going
on "today". But what I would like is to be able to see this
information for any particular week of the past year or even the future
providing information is in the database. What I am thinking is to
create another table called hours that will be the calculated amount of
time per day that I am getting now for each employee into a field with
a date. Then have a macro or something run a query to put this into the
table. Then I could get the information that I want by making a query
based on the info in the new table to get me the capacity for any
employee at any given time period. I cannot figure out how to do this.
Maybe someone has a better idea. I'm open to anything that will help.

 

[Guest]

David:

A couple of things that may help you with this:

1) Make sure you have a table that associates Employees to Projects, and
how much time the Employee spends working daily, on the Project.

2) To project into the future, you will need to have some way of measuring
the complete amount of time, to the project, and then reduce that total, with
the daily figures from Employee time. It would seem that this must always be
considered an estimate, as the world is always conspiring against Projects
getting completed on time.

3) Going in to the past would actually be similar to your future
projections, except at this point your calculations should be with known,
actual numbers. Are you capturing your Employee's actual time worked vs.
their projected time worked? It would seem this information would be
essential to provide useful data about past performance.

Good luck.

Sharkbyte

 

[david]

Thanks for your reply. I very much appreciate your time and efforts.
Yes, I do have the tables the way they should be. But no I do not have
the amount of time that the employee actually spends working on a
certain project. I have the estimated time that it should take in a
field. Then I take the due date minus the start date so I know how many
days to complete and then divide this into work hours so I know how
many hours per day they will spend on that project theoreticly. They
may have 10 different projects going on with different times each day
depending on when it's due. I don't care how they spend their time as
long as everything gets done. If they finish early, then the due date
is moved up. If it take longer, then it's pushed back. What I want to
be able to see is when an employee has to much or to little on their
plate. I do not have a field for daily hours spend on the projects.
This is what I'm trying to come up with. The report that I have now
shows the total hours of all the projects combined that each employee
has. If I could somhow take that number which is a calculated value at
this point and put it into a feild as a constant value now along with a
date. If I could run a macro or something everyday to do this, then I
could make the query for any date in the past that I wanted to see what
the time capacity each employee had at any point. This would not be
exact, but for what it's for would be close. What you say about
capturing the employees actual time would solve everything I think. I
could then have everything right there but I don't want the employees
to be tied down with all this. Some employees work on 4 or 5 different
projects at the same time. Sometimes they may stop in the middle of one
and do something else and then go back to it.

worker1 totDailyHrs Submit NeedBy EstHrs Remaining
Hrs.perday
3.68 1/31/06 2/28/06 57 18
2.37
1/31/06 2/23/06 26 14
1.31

This is basically my report. This worker has 2 projects going on. The
first one needs to have 2.37 hours of their day spent on it and the
second 1.31 hours per day spent on it. This shows me that they have
projects that take up 3.68 total hours of their day. The worker may
choose to spend their whole day working on project 1. That's fine, as
long as the rest get done before the due date. This tells me that this
person can take on another project also. If they had a total of 10
hours per day, then they have too much. I want to somehow take this
3.68 total hours and put it in another field of a table along with that
date to keep as an actual number now (not calculated anymore) Then I
can get exactly what I want. Does this make more sense of it?