asked on

Create Timeline chart from Access Data

[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.

Barbara

nico5038

You can use the standard graph control for this when you have the count per year.
As you have however a range in your data (Start and End year) you can't use a normal query as the intermediate years won't be showing a result.

One solution is to create a small piece of code to generate a table ("tblGraph ") with for each congregation a row with the year they are active. Then that table can be used for the regular graph.

Create a table with Congregation and ActiveYear.

Now use this to fill the table:

function fncCreateYears()

dim intI as Integer
dim rs as DAO.recordset

set rs = currentdb.openrecordset("tblCongregation")

' test for data found
if rs.eof and rs.bof then
exit function
endif

' empty tblGraph to make a rerun possible
currentdb.execute ("delete * from tblGraph")

rs.movefirst
' process all rows
while not rs.eof
' create for every year a new record
for intI = YearFounded to IIF(IsNull(rs!yearclosed),Year(Date),rs!yearclosed)
currentdb.execute ("insert into tblGraph (Congregation, ActiveYear) Values ('" & rs!Congregation & "'," & intI & ")")
next
rs.movenext
wend

end function

This code needs to be copy/pasted in the modules section and you need to open the Tools/References when in the code to see that the "Microsoft DAO version #.#" library is checked.
Finally type in the Immediate window at the bottom:
call fncCreateYears
and pres [Enter] to generate the table for your graph.

Nic;o)

patrickab

BarbMpls,

Please provide a link to your data set so that we can create a chart to illustrate the findings.

Patrick

BarbMpls

ASKER

Thanks for the idea, Nico5038. I'll have to play with it this morning to see if it will achieve the result I want.

Patrick, I'll need to locate a space to post the data, and then will do so. I hope to have that done later today.

nico5038

You can use www.ee-stuff.com for uploading.

Nic;o)

BarbMpls

ASKER

I have exported the relevant table to a new database, which is available at:

https://filedb.experts-exchange.com/incoming/ee-stuff/76-EEQuestion---BarbMpls.zip

I deleted most of the irrelevant fields, to keep it simple.

nico5038

Had some typo's and changed the [Year Closed] field into YearClosed.
Check: https://filedb.experts-exchange.com/incoming/ee-stuff/77-EEQuestion---BarbMplsSample.zip

Nic;o)

ASKER CERTIFIED SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BarbMpls

ASKER

Thanks to both of you for your efforts. I have looked at both files and have decided to accept Patrick's solution. The major reason is that Patrick's solution allows me to present both the graph and the supporting data in an Excel workbook, as my clients are accustomed to seeing. An additional benefit is that the logic behind the calculations is more transparent, so that those on my client's committee who want more than the overview can take a closer look at how the numbers on the graph were generated.

Barbara

patrickab

Barbara,

Pleased it provided the sort of analysis you were needing. Thanks for the grade.

Patrick