?
Solved

Create Timeline chart from Access Data

Posted on 2006-05-12
9
Medium Priority
?
3,140 Views
Last Modified: 2008-01-09
[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
0
Comment
Question by:BarbMpls
  • 3
  • 3
  • 3
9 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 16672769
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)
0
 
LVL 45

Expert Comment

by:patrickab
ID: 16673305
BarbMpls,

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

Patrick
0
 
LVL 1

Author Comment

by:BarbMpls
ID: 16673676
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 54

Expert Comment

by:nico5038
ID: 16673704
You can use www.ee-stuff.com for uploading.

Nic;o)
0
 
LVL 1

Author Comment

by:BarbMpls
ID: 16673812
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16673928
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)
0
 
LVL 45

Accepted Solution

by:
patrickab earned 1200 total points
ID: 16674124
BarbMpls,

It turned out to be an interesting project...

Here's a file with a bar chart plotting the growth and decline of churches for your data:

http://my.storenow.net?f=558

Hope it helps

Please ignore the macro as I used to help me assemble the stats.

Patrick
0
 
LVL 1

Author Comment

by:BarbMpls
ID: 16674544
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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 16674742
Barbara,

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

Patrick
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question