Pivot linked to large Access DB...help!
 

I have an Excel pivot table linked to an Access database table via MS
Query. The problem is the pivot pulls in all the table records, which
is about 1M records. I know I can filter the data using the pivot data
"wizard" or MS Query...but, is there a better way?

Maybe there's a way to use worksheet cell values as parameters for SQL
to use as the pivot data source, but I'm not sure how to go about
that.

Any suggestions on how to do this or a better way to filter the pivot
using Excel?

TIA....

Pivot linked to large Access DB...help!
 

Hi John,

Place the 'filter' fields as Page Fields.
Then change the Advanced field settings to 'Query external data source as
you select each page field item'

You can get further info from my website.

Ed Ferrero
http://edferrero.m6.net


"John" wrote in message
om...
I have an Excel pivot table linked to an Access database table via MS
Query. The problem is the pivot pulls in all the table records, which
is about 1M records. I know I can filter the data using the pivot data
"wizard" or MS Query...but, is there a better way?

Maybe there's a way to use worksheet cell values as parameters for SQL
to use as the pivot data source, but I'm not sure how to go about
that.

Any suggestions on how to do this or a better way to filter the pivot
using Excel?

TIA....

Pivot linked to large Access DB...help!
 

Thanks Ed; however, I've tried using page fields as filters but have
been unable to resolve the resulting error message:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(qry_001_XL_Pivot_01_B.Fiscal
Yr=Pa_RaM000)'

I've searched the newsgroup and found that a few others have received
this message, but know one seems to have a solution. Do u have any
tips?




"Ed Ferrero" wrote in message ...
Hi John,

Place the 'filter' fields as Page Fields.
Then change the Advanced field settings to 'Query external data source as
you select each page field item'

You can get further info from my website.

Ed Ferrero
http://edferrero.m6.net


"John" wrote in message
om...
I have an Excel pivot table linked to an Access database table via MS
Query. The problem is the pivot pulls in all the table records, which
is about 1M records. I know I can filter the data using the pivot data
"wizard" or MS Query...but, is there a better way?

Maybe there's a way to use worksheet cell values as parameters for SQL
to use as the pivot data source, but I'm not sure how to go about
that.

Any suggestions on how to do this or a better way to filter the pivot
using Excel?

TIA....