Solved

Project Planning Database

Posted on 2004-09-02
9
445 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 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