Pivot table in Excel based on Access table

[LGOF62]

I had an access table with 105,000 records and 33 columns (3 numeric). From Excel (2002) I made an ODBC link to this table in Access (2002) using microsoft query and it worked fine.

I then added a numeric column and about 5,000 records and suddenly I can no longer extract the data into Excel.

I divided the table into two sets (each with more or less 55,000 records, 34 columns) and I had no problem extracting either sub-set.

I have now got two Pivot tables on the same worksheet, each with a sub-set of the data.

I want to import all the data into one pivot table and the number of records is likely to double.

Q: Have I reached the limit in data that can be imported into Excel?
Is there some way around this?

I would appreciate any help!

 

[chergh]

You can only import around 65,536 rows in the one go, if you import more than that then there won't be enough rows on your excel spreadsheet to accomodate them all, unless your using Excel 2007.

You could set up your import to import the first 65,000 records and then import the second lot of records to a different range.

I would suggest you use SQL to do the data manipulation then import the manipulated data.

 

[LGOF62]

pivot table

I have already imported 105,000 records with no problems because it is a pivot table and not all the rows need to be displayed. I have even imported over 300,000 at one stage but then there were less columns.

 

[stopher]

I have pivot tables that load over 500,000 records and 30+ fields and refreshes the entire data in about 20 seconds Unfortunately I don't fully understand how the pivot table architecture works so I can't explain why it is able to do this - I'm just very glad it can. The method is real easy.

Here's what I do in excel:
- create a pivot table using the pivot table wizard.
- select external data source
- click "get data"
- Select "MS Access Database" under the databases tab
- point to your database
- Select your fields
- Set your filters (this is where you can really affect the refresh performance and file size so only filter what you want)
- Sort if you wish
- click finish

The method does the MS Query for you although it's done behind the scenes. I don't think it uses ODBC (I might be wrong) - but who cares if it works.

The data gets loaded "behind the scenes" in your workbook rather than in a viewable spreadsheet. This probably explains why it isn't limited to the number of Excel spreadsheet rows.

You can drill down on your pivot to see the entire detail (although why would you want to?). However, if I try to display detail beyond the capacity of the spreadsheet I get the message "Not all source records have been copied". So clearly there is a distinction between what it holds behind the scenes and what it can display.

One other key point I found recently. If you want to create multiple pivots in a spreadsheet from the same source data, you create your first pivot as above, then when you create the second using the wizard, choose "Another PivotTable report or PivotChart report" as the data source. You can then select the first pivot datasource. This means that only one data set needs to be kept behind the scenes and hence only one dataset needs to be refreshed.

hth
Chris

 

[LGOF62]

You're not wrong

Chris, With you all the way. I have done exactly as you have described (many times) and in my setup it is based on an ODBC driver.

I have done all my filtering in the access application. I need the table 'as is' in the Pivot report. In my experience all the data is normally loaded into the Pivot cache (you probably get the message because you filter in MS Query) and you can see this as you will have a full list of options in all dimensions (fields).

I just think that I have come to some kind of limit and I am looking for a way around it. Maybe it is the driver?

????

 

[stopher]

Yes, pivot cache! that was the phrase i was looking for.

But I'm able to store the full 500,000 records in the cache (I never bother filtering the source because the performance is good). I'm using Excel 2003 though. Don't know if that is a factor.

 

[LGOF62]

Maybe 2003 is a factor... But I am working in a large company and I this is not a variable that I can play with.

 

[chergh]

Info I could find on pivottable limits:

> Maximum number of PivotTables on a sheet
Limited by available memory
>Maximum number of items in a PivotTable
8,000

>Maximum number of pages in a PivotTable
8,000

>Maximum number of row or column fields in a PivotTable
Limited by available memory

>Maximum number of page fields in a PivotTable
256 (May be limited by available memory)

>Maximum number of data fields in a PivotTable
256

>Maximum number of calculated item formulas in a PivotTable
Limited by available memory

 

[LGOF62]

This doesn't really help me. Thanks a million, in any case, for having a go.

I'm off to work in my garden.

 

[LGOF62]

A New dawn... a new day... any new ideas?

 

[chergh]

Have you tried removing the numeric column and seeing if it works without it?

 

[LGOF62]

Not yet but I will. I think that it will run but this won't help me, I think.

greetings,

 

[petehilljnr]

If the pivot table you want in Excel is static (i.e. you don't want to manipulate the pivot table layout / fields etc), you could create the pivot table as a cross tab query in Access and extract data from that into Excel.

It doesn't answer your question but could be a workaround?