pie chart based on tmpTable having single record (1 Viewer)

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?

Could you post some sample data for the Pie Chart.

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.

Is this what you're looking for?

sample report with chsrt

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?

you need to add reference (@ vba, tools->reference) Micrisoft Excel XX. X Object.
excel has a very professional looking chart. access, never mind.

herevivremove the reference from excel and use late binding.

Here is a sample.

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.

You're welcome, good luck.