I'm using MS Access 2010. I'd like to add a simple pie chart to the footer on one of my reports. The pie chart would be based on a temporary table having only one record containing six fields. Those six fields (currency values) correspond to the pie wedges I want to chart--when summed they constitute 100% of the "pie". The temporary table data isn't directly related to the data displayed on the report, but when presented in a pie chart the temporary table data may help users understand some aspects of the 'big picture'. I thought it would be easy to create a pie chart, but I'm getting nowhere with the chart wizard. Can anyone point me to a post that will get me started in the right direction?
pie chart based on tmpTable having single record
- Thread starter Blake
- Start date
Yes; here's an example:
Available Funds $24,959.94
Fee #1 -$ 3,683.36
Fee #2 -$ 2,341.18
Fee #3 -$ 146.25
Fee #4 -$ 1,878.91
Net Remaining $16,910.24
My original post mis-described the data. The "Available Funds" constitute 100% of the pie; the other five fields are the "wedges" that I'd like to chart.
Available Funds $24,959.94
Fee #1 -$ 3,683.36
Fee #2 -$ 2,341.18
Fee #3 -$ 146.25
Fee #4 -$ 1,878.91
Net Remaining $16,910.24
My original post mis-described the data. The "Available Funds" constitute 100% of the pie; the other five fields are the "wedges" that I'd like to chart.
JHB: Yes, that's exactly what I'm looking for.
Arnelgp: Thanks for your sample database. Looking at your Report_Open code, I'm guessing that the idea is to do some preparatory charting work in Excel, then import the result into Access and adapt from there. Is that the general idea? I've done some basic charts in Excel, but I've never seen the underlying code. Also, when I run your report, I get an error ("can't find project or library") on the 'Chr' function call in this statement: xlsh.Range(Chr(65 + i) & 1) = fld.Name Can you tell me which library I need to load to get that function?
Arnelgp: Thanks for your sample database. Looking at your Report_Open code, I'm guessing that the idea is to do some preparatory charting work in Excel, then import the result into Access and adapt from there. Is that the general idea? I've done some basic charts in Excel, but I've never seen the underlying code. Also, when I run your report, I get an error ("can't find project or library") on the 'Chr' function call in this statement: xlsh.Range(Chr(65 + i) & 1) = fld.Name Can you tell me which library I need to load to get that function?
Arnelgp/JHB:
Thank you BOTH, very much. I'm intrigued to see two quite different approaches which both give the desired result.
I'm working well beyond the limit of my ability here, but I'm trying to understand.
JHB's approach seems to bring the Microsoft Excel graphing capability into Access, allowing me to use my (admittedly basic) Excel charting knowledge inside Access. I can definitely work with this solution.
To my surprise, I also managed to get Arnelgp's solution working, despite some initial hiccups. As far as I can tell, Arnelgp's code uses the Microsoft Excel 16.0 Object library. My system flagged that as "missing". Instead, I've got the Microsoft Excel 14.0 Object library, which I added to my list of references. But, I then got a variety of compile errors, such as "Object doesn't support this property or method" on this line of Arnelgp's code:
xlsh.Shapes.AddChart2(262, -4102).Select
After a bit of research I guessed that my system (or library??) doesn't support the AddChart2 method, so I tried the AddChart method instead. That worked, but it produced a bar chart instead of a pie chart. After checking the AddChart method syntax, I realized that AddChart doesn't accept a style parameter (the '262' in Arnelgp's code above). So I wound up substituting this:
xlsh.Shapes.AddChart(-4102).Select
and it works!
Once again, thank you both very much.
Thank you BOTH, very much. I'm intrigued to see two quite different approaches which both give the desired result.
I'm working well beyond the limit of my ability here, but I'm trying to understand.
JHB's approach seems to bring the Microsoft Excel graphing capability into Access, allowing me to use my (admittedly basic) Excel charting knowledge inside Access. I can definitely work with this solution.
To my surprise, I also managed to get Arnelgp's solution working, despite some initial hiccups. As far as I can tell, Arnelgp's code uses the Microsoft Excel 16.0 Object library. My system flagged that as "missing". Instead, I've got the Microsoft Excel 14.0 Object library, which I added to my list of references. But, I then got a variety of compile errors, such as "Object doesn't support this property or method" on this line of Arnelgp's code:
xlsh.Shapes.AddChart2(262, -4102).Select
After a bit of research I guessed that my system (or library??) doesn't support the AddChart2 method, so I tried the AddChart method instead. That worked, but it produced a bar chart instead of a pie chart. After checking the AddChart method syntax, I realized that AddChart doesn't accept a style parameter (the '262' in Arnelgp's code above). So I wound up substituting this:
xlsh.Shapes.AddChart(-4102).Select
and it works!
Once again, thank you both very much.
Similar threads
Solved
Issues with Pie Chart
