Project Planning Database

I want to create a database that can be used for project planning, the project plans will be available to all users, for both viewing and modification as all users have MS Access XP on their computers. I know that ideally planning should be done with MS Project but we only have a couple of licences for that, and a number of people are not very computer literate so would not be able to use it. I want to provide a report that looks very similar to a MS Project plan.

Does anyone have any ideas as to the best way to go about this / most appropriate solution? If anyone has any examples that would be brilliant, either in Access or VB. Thanks in advance for any help.

Lianne

LVL 1
Lianne072600Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Ohhh ... welcome to my world ... I've been knee deep in building a Project Management db for the past year and it's still evolving (version 6.6 so far, with version 7 about 2 months out - prototyped in Access, migrating to VB due to specific needs of the VB interface). MS Project is great for really, really big projects; for small projects, it's just way too much.

What do you want to track in regards to the Project? Are you going to manage human and logistical resources? Are you going to actually manage scheduling (i.e. John will work on Task#5 on Project #87 on Monday and Tuesday, then he'll be available until Friday, when he goes on vacation)? Are you going to manage skillset planning (i.e. Sue is ESD Certified for Training, therefore Sue must be availabe on Wednesday, June 1 to lead an ESD class ... we have to build PartB on Wednesday, and Sam is the only one who can perform Taks #923 which is integral to this build, therefore we must make sure that Sam is available on Wednesday)?

Be prepared to spend a LOT of time in the requirement gathering phase ... this, more than anything, will help you to in the long run. Run through MS Project and make a loooong list of the things you like/need, and things you don't like/don't need. Talk to people regarding what actually needs to be included in this application. What do they want to accomplish? Just a list of tasks? This is simple; a few tables would accomplish this. When you begin to tie tasks to people/groups/skills etc ... this is where the complexity comes into play.

Alternatively, have you considered purchasing a ready-made solution? There are several project management packages out there (I used to have a list somewhere; used them to, umm, "borrow" interface ideas, etc) that may suit your needs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Colonel32Commented:
I suspect that this would quickly become as large a project as the ones you wish to plan! Outlook already features many tools which can be use to this effect - depending on how detailed you wish to be.
0
Lianne072600Author Commented:
LSMConsulting -

>>Are you going to manage human and logistical resources?
Yes

>>Are you going to actually manage scheduling (i.e. John will work on Task#5 on Project #87 on Monday and Tuesday, then he'll be available until Friday, when he goes on vacation)?
Yes, including engineers unavailable time

>>Are you going to manage skillset planning (i.e. Sue is ESD Certified for Training, therefore Sue must be availabe on Wednesday, June 1 to lead an ESD class ... we have to build PartB on Wednesday, and Sam is the only one who can perform Taks #923 which is integral to this build, therefore we must make sure that Sam is available on Wednesday)?
Yes

>>Alternatively, have you considered purchasing a ready-made solution?
No, as I like the customisability of something like access / vb. I also find that I can learn something along the way, which has to be a good thing. Are there any ready-made solutions you would recommend looking at?

Colonel32 -
The majority of people within our company do not use Outlook a great deal, I myself don't have such an in depth knowledge of it, or it ability to provide some of the features you can get with a program such as access. There are also issues of security as many of our machines do not allow you to open other users folders / calendars etc.

Thanks for the input

Lianne

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Lianne,

Hope you've got a good bit of time on your hands ... I've put in roughly 500+ hours, and I've got a LOT of experience in Access/VB/VBA.

HR - Basically, a table containing your people, a second table containing known "skills", and a join table ... for example:

tblPeople
=======
lngID
strName
etc etc

tblSkills
=======
lngID
strSkill
etc etc

tblSkillSet
======
lngID
lngPeopleID
lngSkill

So ...

lngID       strName      (tblPeople)
-------------------
1             Scott
2             Sue
3             Bill

lngID   strSkill     (tblSkill)
-----------------------------
1         ESD Trainer
2         Cert 54 Trainer
3         ET Integration

lngID     lngPeopleID    lngSkillID  (tblSkillSets)
------------------------------------
1             1                    1
2             1                    3
3             3                    1
4             2                    2
5             3                    2

You could then query this table to determine those people who provide the necessary skillsets for a task ... for example, let's say I needed find someone who was (a) Cert54 Trainer and (b) an ESD Trainer:

SELECT lngPeopleID FROM tblSkillSet FROM tblSkillSet WHERE lngSkillID=1 OR lngSkillID= 2

This would result in 1 record (lngPeopleID=3) ...

RE Scheduling ... this is a topic in and of itself ... my client currently handles this with a different application, and my application doesn't integrate with this yet (we're actually considering moving the scheduling to my application, but I haven't done much in the way of specs and requirements).

Basically, you need to (a) enter ANY time that a user is NOT avaialble ... IOW, you ASSUME that a user is available 24/7 (obviously this isn't the case, but for schedulilng purposes we do this). Therefore, when Scott is taking vacation from July1 - July10:

tblSchedule
===========
lngID
lngPeopleID
dteStart
dteEnd

We add a record:

INSERT INTO tblSchedule(lngPeopleID, dteStart, dteEnd) VALUES(1,#07-01-2004#,#07-10-2004#)

Then, when our SkillSet routine determines that we MUST have Scott to perform Task#3:

we first query tblSchedule:

SELECT lngID FROM tblSchedule WHERE lngPeopleID=1 AND (dteEndDate>Date())

This returns all records in tblSchedule which have not been "completed" (i.e. where the EndDate is in the future). From here, you would write code to determine (a) the dates of the task and (b) the dates upon which the specified user would NOT be available (perhaps an array ... I'm still researching this) and (c) compare the two. Note that this is fairly simple with one user ... when you have to make sure 2 or more users are available, the code can get quite complex (at least at first glance for this ...).

RE Skillset planning ... this is really answered above ...

I agree with building your own ... as you say, much more control over your end result, but also a LOT of time. Wish I could speak more to the scheduling aspect of this, but as I say, this is something I've just recently begun to explore. I'd suggest Googling on "hotel reservation system" ... these are extremely schedule oriented, and would likely give you some good ideas in this regards.

Also, regarding interfaces, there are some pretty neat scheduling controls out there (if you decide to migrate this to VB) ... they may work in Access, or you could of course "roll your own"

0
Lianne072600Author Commented:
LSMConsulting -

Thanks for the info.

Have you any idea of the best means to implement the reporting side of this? I am hoping to achieve a printout something like a scaled down (more simplistic) version of an MS project plan, that shows the task details, dates and who will be working on it. I don't mind putting in the work to get exactly what I want.

Lianne
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Not really ... I recall a newsgroup posting with some links to screenshots of reports, and essays on how best to build them ... I'd have to go back and look ... probably be a few days before I can find them (changed machines recently and I"m not sure I copied all those items).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.