Capacity planning

[doctorswamp]

Hi

I'm stuck for ideas how to even start this. A job form stores start and end dates of various jobs, and the man hours needing to be provided during each job.

So Job xyz
- starts 1/3/07
- ends 15/6/07
- needs 30 man hours

If resource available is say 8 men doing 40 hours per week I'd like to be able to chart the total hours available per day, and the hours required per day for all jobs currently running. This would show at a glance any spare capacity or over commitment.

Eventually it would be good to divide the man hours into particular skills and do more complex planning, but first things first.

Any suggestions or pointers? FAQ didn't respond to capacity planning.

Many thanks

 

[MichaelRed]

It is possible to do some ammount of project scheduling via your own ("home grown") design and implementation ... but I don't advise it.

You can buy Ms. Projects. for probably less than ~~ $500.00. Figure out your hourly rate (even at the minimal cost you might assign for what your own time is worth to you ... say $5.00 / hour) divide this into whatever your actual cost for the existing software and see how many hours you can afford to invest before the labor cost overwhelms the purchase price. Even taking the $500.00 @ $5.00 / hour and assuming you are REALLY REALLY interested in furthering your skills in software desig and analysis (say a factor of 2*?), you would need to complete the design AND coding in ~~ 200 hours or five weeks.

Given your aparent capability in the design (based solely on this inquiry). The answer (at least for me) is to run (NOT walk) to the nearest source of shrink warpped software and shell out the cash. In lesss than the 3 to five weeks of your effort you should be well beyond even the extended answer you ask here and have a more professional looking 'product'.

more or less obviously there is more to the 'story' than your post (or my response) so please embellish the story line a bit so someone may provide a response useful to you.




MichaelRed

 

[doctorswamp]

Point taken, thanks.

It would still be very convenient to have some simple indication of available capacity within Access if possible.

I was thinking that given a job with a start and end date, and a requirement for man hours, I could calculate the average hours per day over all of the working days in the period. Then add the averages per day for all other jobs and simply compare the total with (No of staff)*8 hours.

I can't think of a way of creating fields for each day and allocating averages to them.

 

[MichaelRed]

you will just sink deeper into the morass. If you want to take up programming as a hobby, go to your local bookstore and buy some tutorial material and a starter copy of the programming 'language' you feel interested in. IF that turns out to be a database system, get a book (or at least be sure the programming one includes a section) on RELATIONAL Database. In particular, the relarional database part is one of those death defyingly simple things ... which can tie the novice up in knots over the complexity.

While laying out the tables and fields for the minimalist application you describe can be done by many members of thes fora, whatever was provided would probably not be usefulk beyond the imediate thought. A far better investment in your avocation would be to get the book/tutorial, work your way through it, do the exercises, at least make an attempt at establishing the necessary database objects, user interface, and rudimentary program before asking meaningful questions.

I am NOT truing to discourage or disparage your thoughts, but presenting what I believe will serve your purposes much better that 'hanging out' in these (or other programming) villages attempting to build a real application (particularly if it imnvolves a data base) gertring snippets of advice on an ad-hoc basis.




MichaelRed

 

[doctorswamp]

Michael

Okay, I surrender, and thanks for your time.

David

 

[Eupher]

I don't mean to pile on, but MichaelRed speaks truly. My own experience might help illuminate a little. My organization had need of a custom scheduling app. I say "custom" because I really did search for an "off-the-shelf" solution and finally concluded that none existed that would adequately address our needs. They all were 1) overkill; 2) prohibitively expensive; 3) would still need *extensive* customization. In fact, our parent organization had contracted a developer to design a product. After several *years* and many thousands of dollars (the developer never talked to our outfit about what we needed this program to do) - we ended up with a program that was hopelessly broken - and wouldn't really do what we needed even if it worked right.

So I came to the conclusion that if was going to happen, I was going to have to do it.

At the time, my database experience was minimal: I knew how to create tables, simple queries, and reports. But I had only a fuzzy notion of how a relational database worked, and had no VBA programming experience at all (many years ago I had taken a couple of classes in COBOL and C). I knew the temptation would be to dive right in and start hacking away. I also knew this would be a shortcut to frustration and headaches. So I decided I would buy books and tutorials and learn how to design and program a relational database (specifically Access) from square one.

Over the course of a year I spent ~700 hours working through the lessons and actually building the DB - and many more since that time updating and tweaking. The app has been in use by our staff for about 4 years now. I still call it a beta, though. It does *everything* we wanted it to do; but there are still a couple of (non-critical) UI issues that I need to iron out.

My point: don't re-invent the wheel. I decided to do my project only after I became convinced that there was no commercial product that would truly meet our needs. If that is true for you also, then do it, but do it right. Learn the concepts and vocabulary of Access and VBA before trying to apply them to *your* problem. You will *still* have many frustrations and questions, but you will be in a far better position. And, as in my experience, you may find it a very rewarding learning experience.

Good luck!

Ken S.

 

[doctorswamp]

Thanks both.

Just as a mild defence I too have been learning VBA for quite some time and have been able to provide solutions to some very tricky problems during this time - with the unbelievable assistance of Tek-Tips folk.

Without taking the easy way out, and after checking the web and FAQs, I posted the question in case anyone had had a similar requirement. I am continually amazed and delighted by what comes back to questions on a wide range of subjects.

All the best

David

 

[Eupher]

David,

I didn't mean to imply that you were a beginner or lacked coding skills. Nothing wrong with posting to see if someone has done something similar - but I think the consensus is that these sorts of things are not trivial and may require hundreds of hours to develop. But your initial question seemed a little general in nature, and we're better at specifics.

Ken S.

 

[MajP]

Dave,
Make the investment into project. If you enjoy coding and VBA you can really make some nice applications using Access, Project and Outlook tied together. Project is extremely robust and I highly recommend getting a good book because it is not easy to learn on your own especially the finer points. Also there are a couple of freeware versions out there, ex:

http://www.dotproject.net/

I have not used it, but recommend you take a look