Gant Chart type display

[petro62]

I have looked around and I know creating a gant chart in access doesn't seem to be an easy task so maybe there is an alternate solution.

I created a query that lists all of the project names and along side of them their milestone dates (feasibility, conceptual, definition, design, construct, startup, turnover)



I am wanting to make a report that will allow me to select all active projects and show how those milestones overlap with each other during the calendar. That way we have a nice visual of potential resourcing conflicts. A gant style visual was what first came to mind, but maybe there is some other visual that would work.

[Micron]

Two things come to mind: report textboxes that don't actually contain any data, have background shading and their widths are consistent with a percentage. However, if you want the horizontal position to align with something (e.g. a date) that presents an issue I wouldn't bother trying to solve in Access. I'd go with plan #2 - create the chart in Excel. You should be able to get Access data into the chart sheet without too much trouble. You can even use a bound or unbound object control to display the chart in a report if need be. I've always said that Access charts are sorely lacking, to the point that I'd rather rub sand in my eyes than bother with them.

[CJ_London]

gant charts can be fairly easily created using a crosstab query and conditional formatting. All depends on the size of units (days/weeks/months) and the number required. A crosstab has a limit of 255 columns

you need to create a table of dates which will form your column headings. This can be done with a query but for illustration purposes, a table will do

tblDates
CalDate
1/1/2021
2/1/2021
...

...
31/12/2021

you will need to modify your existing query, or start again to have a query result of something like

qryMilestones
project...milestone...startDT......endDT
A...........feasibiity...1/1/2021...23/1/2021
A...........conceptual..5/2/2021...9/2/2021
etc


then create a new query (qry1) to list all the dates which are 'occupied'

SELECT DISTINCT project, milestone, calDate as Evt
FROM tblDates, qryMilesones
WHERE calDate BETWEEN stDate and endDT

you should then be able to create a crosstab from this query by left joining tblDates to it

TRANSFORM First(milestone) as FirstM
SELECT project
FROM tblDates LEFT JOIN qry1 ON tblDates.calDate=qry1.Evt
WHERE calDate BETWEEN #2021-01-01# AND #2021-03-01#
GROUP BY project
PIVOT calDate

where project is the rowheader, calDate the column header, and milestone the (first) value. The WHERE clause you will need to limit the number of columns to a mximum of 254 (1 is used for project)

To show this in a form, you will need to specify headers -so generally easier to build this in VBA

Once you have your headers you can create your form or report. Use conditional formatting to colour the control back and fore depending on the value of firstM

Anyway, that is one way to do it, other may suggest alternatives

[petro62]

Access reports are sorely lacking, to the point that I'd rather rub sand in my eyes than bother with them.

Lol now that is one way to describe the pain.

Ajax

I created a Query that puts out the information as follows. You get the project name, project status (active or inactive, etc..) and the the individual milestones. For me the start date of the next phase is the end date of the one prior so i only record start date. Turn Over is just a final (project is fully closed date). If that makes sense. So the goal would be to be able to select (active projects) and it would display all the active projects with a gant type chart showing how the milestones overlap. I will play around with what you posted.

[Micron]

I meant to write charts. Corrected my post. I have no such issue with reports!

[CJ_London]

for a gant chart to work, you need a start date and an end date - if the end date does not exist then use the start date for the following milestone less 1. That just gives you an issue with turnover which you will need to resolve, particularly since you seem to have some projects which only have a turnover date. Note my example was on the assumption of dates - your data appears to be based on months so adjust accordingly and you can go for an extended time period if required.

[June7]

Review https://www.accessforums.net/showthread.php?t=18459

[petro62]

Review https://www.accessforums.net/showthread.php?t=18459

Thanks June7 I saw that one through my searching. I even downloaded the busdrive example that was referenced somewhere on these forums. I will need to dig into it.

I guess the part I am getting confused on is that instead of a gant chart that breaks down milestones in a project per line I want to have a gant chart that has a project per line and then has the month color coded for the different phases in the project. I will have to dig in some more on the thread you linked.

[June7]

Are you familiar with Conditional Formatting of textboxes and comboboxes? Allows 50 rules I think.

[CJ_London]

I guess the part I am getting confused on is that instead of a gant chart that breaks down milestones in a project per line I want to have a gant chart that has a project per line and then has the month color coded for the different phases in the project

that is what I was suggesting and is also the basis of the link provided by June

[petro62]

Are you familiar with Conditional Formatting of textboxes and comboboxes? Allows 50 rules I think.

I am not as familiar with the conditional formatting in access, but I think I can figure that one out with some google searching. The main obstacle for me is just getting it to add the milestones for each project on one line with that project, but I think that busdriver example file kind of shows me the way. Below I attached an example of what I somewhat hoping to achieve. Each line is a project with the different phases marked out.

[June7]

Should be adaptable. Instead of employees you have projects and phase code instead of absence types.

[petro62]

Should be adaptable. Instead of employees you have projects and phase code instead of absence types.

Thanks for the info. It really is a great example and I can see what I need is there, but I am struggling on how to convert it. So in the bus example the QRY puts the respective off code in the proper date of the month with the person ID.

I need it to me the respective phase(off) code in the proper month&year with the project id. I think it is the month and year part I am getting hung up on at the moment.

[petro62]

Sorry to drag up an old thread, but I got pulled away from this project for a bit and I am back on it. I am still stuck in try to adapt the bus driver example provided by June7. Currently in a table called tblProjectDetails I have 7 phases in the project (Feasibility, Conceptual, Def.....) In between those I just have a calculated column of the amount of days between the dates. Each row is a different project.

Some how I need to create a query that associates dates in a specific column with a certain phase in the project. So Feasibility would be 1 and Conceptual 2 and so on and so on. Then from there I think I could figure out how to apply the rest to the busdriver example. (I hope).

[June7]

Provide your db for analysis. Follow instructions at bottom of my post.