Gantt chart for Project Portfolio

[accessbro]

I am trying to create a Gantt-style chart but I do not need to show several activities for a project like a regular Gantt chart. This will help program managers with assigning project managers to projects by showing a Gantt view of all the projects grouped by the resource name on the X axis. The project start and end dates will determine the width of the Gantt bars for each project.



I want the Gantt bars to be colored/outlined depending on if a project manager's project is overlapping with another. For example, Project A is from 4/9/2012-3/31/2015 and Project B is from 11/1/2013-9/30/2015. The Gantt bar for Project A would be outlined purple from 4/9/2012-11/1/2013 to show there are no other overlapping projects during that time. From 11/1/2013-9/30/2015, both project A and Project B would be outlined in red to show that the project manager is working on both projects. From 3/31/2015 - 9/30/2015, Project B would be outlined in purple because Project A has ended and Project B is now the only project the project manager is working on during that time.

For my tables I have:

1. tblProject
   
   • ProjectID
   • ProjectName
   • ProjectStartDate
   • ProjectEndDate
   • fkProgramManagerID
   
   
2. tblResource
   
   • ResourceID
   • ResourceName
   
   
3. tblProgramManager
   
   • ProgramManagerID
   • ProgramManagerName
   
   
4. tblResourceProjects
   
   • ResourceProjectID
   • fkProjectID
   • fkResourceID
   
   

I'm having trouble figuring out how to create the Gantt chart from here. I think I would need to use VBA to color code the Gantt bar but I'm not sure how to apply different colors based on if there's overlap with another project or not. I was thinking I'd need to create a table like tblDates with all the dates of every single day from 1/1/2012 all the way to 12/31/2017. Then I'd use a query or function to compare all the dates in the table to the project start and end dates and add all the dates between the start and end date to tblResourceProjectDates along with ResourceProjectID. Then I'd somehow compare the date and ResourceID for that record and see if there are other dates that have the same date and resourceID and maybe have a yes/no field for overlap and make it yes if there is an overlap and no if there isn't. Then I could apply the styling by VBA that way.

I manually created an example in Visio but I'd like to make this in Access so the Gantt is generated automatically after the user enters the Project and Resource information into a form.

[June7]

Maybe find something helpful in https://www.accessforums.net/databas...ses-18459.html

Don't think it addresses coding overlap.

Project management has been topic of numerous threads, might search forum. There are certainly OTS apps available. Search web.
http://www.pcmag.com/article2/0,2817,2380448,00.asp

[orange]

Did you consider a project management software? There may be some that could do what you need right out of the box.

There is a very basic sample in acc2000 here and an appointment style calendar here.

Good luck. Let us know what you decide/do.

[accessbro]

This is for my employer and the manager I'm working with has very specific requirements so I'm not sure if there is existing pm software. Eventually we want it to have record level version control where it creates a new record when the user edits a record.

Am I at least going in the right direction with how I described it?

[June7]

Well, "creates a new record when the user edits a record" sounds like an audit log. http://allenbrowne.com/AppAudit.html

[accessbro]

I'm not too worried about the audit log for now. The part im really having trouble with is coloring the different parts of the gantt bars

[June7]

Got all I know in the referenced thread. How much of it have you reviewed? It has a couple more references in post.

[accessbro]

I found that thread and several other threads yesterday when I was searching for a solution but I couldn't find anything about color coding different parts of the boxes based on project overlap

[June7]

Probably because it is not possible to color code different parts of boxes. Boxes do not have 'parts'.

[accessbro]

Then maybe I'd have to create several boxes and space them closely together. I couldnt find anything on that

[June7]

I don't even know if professional software accomplishes that. I suggest you just plot the schedules and let people visually analyze them and spot the overlaps. That's really a main reason for presenting data in this graphical construct.

[orange]

I don't have any affiliation with this company or product, but I did find it on google.
It says it's free and is Excel based. Perhaps you can use it directly (all excel) or possibly send data to it from Access.
http://www.vertex42.com/ExcelTemplat...ntt-chart.html

Even the Pro version at $39.95 seems a steal if it does any of what you want.

Update: I also found this link to an Access based Interactive Gantt chart

My recommendation is for you to gather all the requirements in detail, and make a list of mandatory, nice to have etc and then compare various commercial packages with your list. I think there are such packages to do what you need, if you cant find it in the links provided.

Good luck with your project.

I used to use a product called TimeLine (years back) and could pass it data from Access or dBase. It must have dropped off the planet or has been bought out by someone else -- I can't find any references to it. It was easy to use, Pert and Gantt, work breakdown, and had great graphics/reports (for the time).

I have tried M$oft project on a limited basis and found it too precise and demanding for small projects.