Linking parameter query from Access to pivot table in Excel
 

I am trying to link an Access query that prompts for several criteria to an
Excel pivot table. When I refresh the pivot table, I want it to open the
database and run the query to update the data in the pivot table
automatically. When I try to link to the data source, when I get to the end
of the "Query Wizard", it gives me the error of "Not enough parameters.
Expected 4". But it never prompts me for the parameters. Please help. Is
there a setting I need to change or do I link the query differently to avoid
"Microsoft Query" tool?

You can't create a pivot table from a parameter query.

In Access, create a query without parameters, then add the parameters in
MS Query. Nick Hodge has information on his site:


http://www.nickhodge.co.uk/gui/datam...taexamples.htm


ken1975 wrote:
I am trying to link an Access query that prompts for several criteria to an
Excel pivot table. When I refresh the pivot table, I want it to open the
database and run the query to update the data in the pivot table
automatically. When I try to link to the data source, when I get to the end
of the "Query Wizard", it gives me the error of "Not enough parameters.
Expected 4". But it never prompts me for the parameters. Please help. Is
there a setting I need to change or do I link the query differently to avoid
"Microsoft Query" tool?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Linking parameter query from Access to pivot table in Excel
 

Not sure if anyone's still looking at this, but I figured I'd add my two
cents. I managed to get something like a linked parameter pivot table.

From MS Query it is not possible to link a pivot table to an MS Query.
However I have found out that by working with the pivot cache you can get
around this. If you have a cell that you want as a parameter, for example,
what you'd have to do is reference it in a macro and pass it through the
pivot cache. This will effectively allow for a "linked parameter query" for
pivot tables. When you change the cell, run the macro and it will update the
pivot table. Just make sure that if you're using dates have it in the
correct format. Also if you don't know SQL it could be a little confusing to
work with the pivot cache. I know I had a fair amount of trouble before I
finally got it to work. If you need help with the pivot cache, just search
for that on this website. It helped me.

Cheers

Scott
"Debra Dalgleish" wrote:

You can't create a pivot table from a parameter query.

In Access, create a query without parameters, then add the parameters in
MS Query. Nick Hodge has information on his site:


http://www.nickhodge.co.uk/gui/datam...taexamples.htm


ken1975 wrote:
I am trying to link an Access query that prompts for several criteria to an
Excel pivot table. When I refresh the pivot table, I want it to open the
database and run the query to update the data in the pivot table
automatically. When I try to link to the data source, when I get to the end
of the "Query Wizard", it gives me the error of "Not enough parameters.
Expected 4". But it never prompts me for the parameters. Please help. Is
there a setting I need to change or do I link the query differently to avoid
"Microsoft Query" tool?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html