Pivot chart to show progress in multiple projects over time

[Metta]

I'm aiming to create a stacked area chart to display the progress of multiple projects over time, so we can review and forecast project load over time.

The data comes from this table:
tblProject
* ProjectID
* DateProspect
* DateConfirmed
* DateStarted
* DateComplete

A project passes through the statuses Prospect, Confirmed, Started, Complete. The current status of each is calculated in qryStatus from the dates in tblProject. The past and forecast statuses for each project will probably be calculated each time the chart is created (or maybe not necessarily so?)

To create the stacked area chart, the x-axis will be months (e.g. Sept 2013 - July 2015). On the y-axis will be the count of ProjectID, and the series will be the different project statuses.

The sticking point is getting from the Date* fields to the past and forecast statuses in each month. After that I imagine it'll be reasonably straightforward to put into a pivot/chart.

I'm familiar with tables, queries, forms and some SQL, but have no experience of macros/VBA yet.

Any pointers on how to get from these data to the chart will be gratefully received!

 

[Metta]

Update:

The working solution for the time being is a query of businessID and the dates, then import the query into Excel.

The Excel file uses:
- a table of the imported data, with names defined in columns
- a table in which all the cells contain a generalised formula which identifies the status for each month (in columns) based on the dates in the defined-named columns, and sets a code in the cell for the right status of the project in that month
- a table which counts up the cells in each month with each code - the stacked area chart is plotted from the counts in this table.

I haven't yet cracked an Access-only solution, and for the once or twice a week use of this chart, the Access/Excel combo will do at the moment.

 

[pr2-eugin]

I am using Excel Charts in Access over pivot charts, basically got my idea from Roger's Access Library. It works great for me. I am sure you will be able to take it from there.

 

[Metta]

Thanks for the heads-up, Paul. This looks really interesting. Now, to find some time to implement it...