Solved

Project Planning Database

Posted on 2004-09-02
9
435 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:Lianne072600
  • 3
  • 2
9 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 11962127
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
 
LVL 4

Expert Comment

by:Colonel32
ID: 11962133
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
 
LVL 1

Author Comment

by:Lianne072600
ID: 11962774
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 84
ID: 11963018
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
 
LVL 1

Author Comment

by:Lianne072600
ID: 11963340
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
 
LVL 84
ID: 11963463
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now