Word, OLE, Access, & Pivot Charts

[Guest]

I'm have a report that (aside from being a headache) has some Access pivot
charts currently being displayed as images (Print screen). This is how I was
shown how to do the report, and now the requirement is to automate the report
as much as possible. I have about 6 different SQL queries that pull the data
from the same source, the Access is the only unique data source (not really,
there is a query that is run against the scan density [dbccshowcontig] of the
DB and the results are sent as a flat file and exported into Access, which
generates the pivot chart).

So the question(s) is/are... Can I have the pivot chart from Access
displayed in the Word document as a linked object? Can I make changes
directly from Word or would I have to make them in Access? (Changes would be
which of the tables to show in the pivot chart, this criteria is based on a
different set of data earlier in the report).

 

[CyberTaz]

I don't claim to be an authority on this, but I would tend to believe that
the answer to question #1 is "No" - which renders #2 moot. If it *can* be
done it would most likely take some rather sophisticated programming

My reasoning is that Access implements Pivot Charts as viewing modes for
other objects, not really as objects themselves. Everything in Access
revolves around the data content of the Tables rather than the tools
provided in the program to interact with that data.

As an alternative you might look at Excel as a Pivot Chart source. It can
look to the db via a query, plot the Pivot Chart as an object (chart sheet),
and you could link to the Excel file. Word can't edit the object, but as
long as the Excel file is available dbl-clicking the embedded object in the
doc would open the file in Excel... Just a thought.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Guest]

I figured as much, I didn't see anything "obvious" as far as OLE in Word. I
was looking to go the 3 way route as it was (word-excel-access) on the pivot
charts.

THANKS!!

I don't claim to be an authority on this, but I would tend to believe that
the answer to question #1 is "No" - which renders #2 moot. If it *can* be
done it would most likely take some rather sophisticated programming

My reasoning is that Access implements Pivot Charts as viewing modes for
other objects, not really as objects themselves. Everything in Access
revolves around the data content of the Tables rather than the tools
provided in the program to interact with that data.

As an alternative you might look at Excel as a Pivot Chart source. It can
look to the db via a query, plot the Pivot Chart as an object (chart sheet),
and you could link to the Excel file. Word can't edit the object, but as
long as the Excel file is available dbl-clicking the embedded object in the
doc would open the file in Excel... Just a thought.

Regards |:>)
Bob Jones
[MVP] Office:Mac

I'm have a report that (aside from being a headache) has some Access pivot
charts currently being displayed as images (Print screen). This is how I was
shown how to do the report, and now the requirement is to automate the report
as much as possible. I have about 6 different SQL queries that pull the data
from the same source, the Access is the only unique data source (not really,
there is a query that is run against the scan density [dbccshowcontig] of the
DB and the results are sent as a flat file and exported into Access, which
generates the pivot chart).

So the question(s) is/are... Can I have the pivot chart from Access
displayed in the Word document as a linked object? Can I make changes
directly from Word or would I have to make them in Access? (Changes would be
which of the tables to show in the pivot chart, this criteria is based on a
different set of data earlier in the report).