Limit using pivot table with Access database?

[peb]

We are using a pivot table to query data held in an access database. As the
year progresses we reach nearly a million records which it seems that the
Excel pivot table can not handle.
Is this a limitation of Excel or our PC's?

I know we can be more specific to reduce the amount of data during the MS
Query stage, but sometimes this is very inconvenient.

We remove a years data in January, but of course the problem get worse as we
go through the year.

Any suggestions so we can keep all the data we want in our database and
query it as well?

 

[Jim Thomlinson]

You are not limited by your pc but rather by the amount of memory XL can
access. That depends on your version of XL. If you want more info on that
check out

http://www.decisionmodels.com/memlimits.htm

But in short you need to figure a way to limit the number of rows either by
doing some pre-aggregation (group records together) on the access side or by
purging data.