2007 Pivot to Access

[LindaSD]

I have 2003 Pivot tables going to 2003 Access. I can open the pvts in 2007
and refresh with no problem. But I can't base a new pivot off an existing
pivot like I used to in 2003. I am going to Insert, Pivot Table, Use an
External Source, Choose Connection, and I see the Access qry that the other
pivots are using, select it and I get the start of my new pivot table.
However, I notice my file size has gotten huge. Is there something in how
this Connection is working that is pulling in all the same data for each
pivot? I save the data with the file, but in 2003 basing one pvt off another
made for a smaller file. What about 2007? Also, I assume I have to set up a
connection for every qry in my Access database before I start a new pivot?
I have a large amount of data I do this Excel to Access but am not loving
2007 changes. It's a challenge to learn and still keep work flowing. Thanks
for any help.

 

[Roger Govier]

Hi Linda

if you use the icon Existing Connections, the second and subsequent times
through, it will re-use the first connection you set up to your Access
database.
Click Existing Connections>use dropdown to select Connections in this
Workbook>highlight your existing query and just press OK.
You will have a new blank PT created, ready for you to set up report layout,
and based upon the same connection.

Two things for you to consider
Debra Dalgleish has an excellent book on Pivot Tables in Excel 2007
http://www.contextures.com/

Ron Coderre has made a very useful Addin, which helps manage external
connections. This can also be found on Debra's site.
http://www.contextures.com/PivotPlayPLUS.zip