Create Timeline chart from Access Data
Posted on 2006-05-12
[Note: this question is cross-referenced in Excel.]
I'm not certain if I need help creating the right query or creating a chart, or maybe both. Here's my situation:
I'm working with an Access database for a church organization. One of the tables is a list of congregations with (among other details) the year the congregation was founded and the year it closed (that field is, of course, empty for those congregations that are still active).
I would like to create a chart that shows year-by-year how many congregations were active in that year. So the X-axis would be a timeline with years from 1900 to 2006. The Y-axis would show the total number of congregations that were active during each year.
Logically, each data point would need to be something like this: (Count of Congregations where FoundingDate <= Year) - (Count of Congregations where ClosingDate <= Year). I need to make sure that those records where ClosingDate is empty are NOT counted as "less than."
My practice with other work for this client has been to construct a query as needed in Access, export it to Excel, create a pivot table, and generate a chart. That way, they can use the chart for overview presentations, and those who want to peruse the detail can look at the pivot table or the exported list.
I am comfortable working with VBA in both Access and Excel (though a little rusty after not using it for a couple of years), as well as complex formulas in Excel, and will be glad to provide any other information you need in order to help me. I suspect that I could eventually piece something together, but I'm sure you can help me get there more easily.
Thanks in advance for your suggestions.