General Consultant Discussion

Looking for Access (or vba) that will allow me to put an Excel chart in a subreport of an Access report. The report displays Sage 100 Revenue and some related statistics by Customer, so this report is 400 pages long (one page per customer). I need the chart to change as the customer changes on the main report. I know I can do charts in Access but I need a more complex graph that Excel provides.

Any option of flipping this to pull the data right into Excel first? You could use the same query that the Access Report uses as the Excel data source.

Yes. I can, and would prefer to do the charts in Excel. My question is how to embed the Excel chart in the page footer so that it can be linked by customer (the page control). Any help or guidance appreciated!

@DanBurleson is an Access brainiac....

I was thinking to bypass Access completely and use Excel for the chart and report, but this (http://www.utteraccess.com/forum/index.php?showtopic=2003990) seems to show that it is possible. It looks like you want the Excel chart to be alone its own worksheet.

I think this approach works for a single page report but mine changes for each customer. I going to give it a quick test anyway and will report back.

Can you re-write the report in Crystal instead? I never use MS Access reporting since it is (IMHO) inferior to Crystal which all clients already have. Although the charting capabilities in Crystal aren't what Excel provides, it is straight-forward to have a chart in a group footer sub report that directly links to the data in the same group. I would sure like to know how one could link an Excel chart to recurring group level data in Access or Crystal or any application.

No luck on embedding the Excel chart in the page footer. There is no dialog or setting I can find to define a parent/child relationship. I may have to re-write this in Crystal. I'm going to discuss the options with the client and see what they want to do. Thanks for everyone's input.