S
splot!
Hi all,
We've recently upgraded from Office 2000 to Office 2007. On the whole
the transition was pretty painless once I got over the culture shock.
But I have one remaining issue I can't resolve:
I have a number of Excel pivot tables which draw their data from
Access databases. The existing ones, in sheets that were already in
existence before the upgrade work fine, just as they always did. But
I have a problem with creating new pivot tables. I have followed the
Microsoft tutorial to make sure that what I am doing is ok and, as far
as I can see, it is: create a data connection to the Access query
containing the data I want, then specify that connection when setting
up the pivot table. When I do this the fields appear to be there
(showing in the field list) but no actual data is available - the
pivot table stays empty.
I can get round this by copying a pivot table from one of my existing
worksheets and then editing it to give the data I want but this seems
suboptimal to me. I have noticed that the difference between the
copied pivot table and the freshly created one is that in the
Definition tab of Connection Properties, the Command Text has a SQL
Select query in it whereas the freshly created one doesn't.
So I suppose the question boils down to: How do I get my query into
the command text field on the definition tab? The defiinition button
is greyed out on the fresh pivot table so I can't do it that way.
Sorry this is a bit longwinded. Any ideas, please?
Regards
Chris
We've recently upgraded from Office 2000 to Office 2007. On the whole
the transition was pretty painless once I got over the culture shock.
But I have one remaining issue I can't resolve:
I have a number of Excel pivot tables which draw their data from
Access databases. The existing ones, in sheets that were already in
existence before the upgrade work fine, just as they always did. But
I have a problem with creating new pivot tables. I have followed the
Microsoft tutorial to make sure that what I am doing is ok and, as far
as I can see, it is: create a data connection to the Access query
containing the data I want, then specify that connection when setting
up the pivot table. When I do this the fields appear to be there
(showing in the field list) but no actual data is available - the
pivot table stays empty.
I can get round this by copying a pivot table from one of my existing
worksheets and then editing it to give the data I want but this seems
suboptimal to me. I have noticed that the difference between the
copied pivot table and the freshly created one is that in the
Definition tab of Connection Properties, the Command Text has a SQL
Select query in it whereas the freshly created one doesn't.
So I suppose the question boils down to: How do I get my query into
the command text field on the definition tab? The defiinition button
is greyed out on the fresh pivot table so I can't do it that way.
Sorry this is a bit longwinded. Any ideas, please?
Regards
Chris