Large Database Pivot Tables.

I use data from access with 300K+ rows quite a bit, and I've never had a problem pulling them into Excel using pivot tables. You are still limited to 65K rows of data, and the column limits, so you have to format your pivot tables accordingly. Are you saying that you cannot put a field in the Page area on the Pivot Table setup?

I hope I'm posting this correctly...

Thx for responding Scott.

The DB is basically the following:

Cust_code
Prod_code
Cust_Class
Prod_class
sales_1
sales_2
etc etc etc

When I'm defining the layout, I can drag cust code to a row, and the sales fields in the data area. The PT builds

if I drag Prod_class into the PT, and remove customer, evrything is OK. If I de-select a few classes, thereby limiting the prod_codes to 30,000 distinct records (as per MS Query, or ACCESS anyway), I get the message from Excel about one of the data fields having too many dictinct records.

Best Regards
Jon

By limiting your product codes to 30K, does not necessarily mean that your query will return only 30K rows. Even though you are not selecting customer, it will still assign the data to seperate rows using this. You can try using a sum function in your query, or try building your query in access and then using MS Query to pull in the query from access into excel. -just a thought.

OK Scott, THX.
I tried doing this, kinda, but I can't get Excel to use Parameter queries. Am I doing something wrong, or is it just a limitation??

Jon

Sorry about the blank F Up's. I kept pressing the wrong key, new to this type of list.

The question I was trying to ask... would the use of Page Fields help to narrow down the data and if so, why are they dimmed in the advanced section of the field properties??

Thx
Jon

I'm not sure I understand completely. Are you not able to add a Page field in Pivot Table Wizard? To answer the first part of your question, by adding a page field, it could limit the amount of rows in the output as opposed to using the criteria in Row or Column.