Error: Excel can not complete this task with available resources.

[Guest]

Hello:

I have an MS Access database housing about 600,000 records of data growing
at a monthly rate of about 300,000 records. I need to run a pivot table on
this data and refresh it monthly. However, when I try to refresh it this
month I receive the following error:

"Excel can not complete this task with available resources. Choose less data
or close other applications." Then it asks "Continue without undo?" I say
yessss....

Finaly, I receive: "Problems obtaining data."

Can you suggest a solution to my issue?

rgds
David Blisko
Then it gives me: "Excel can not complete this task with available
resources. Choose less data or close other applications." again.

 

[Bill Manville]

I have an MS Access database housing about 600,000 records of data growing
at a monthly rate of about 300,000 records. I need to run a pivot table on
this data and refresh it monthly. However, when I try to refresh it this
month I receive the following error:

Do you need all the data to be retrieved, or could you make the source of the
data be a query which summarises the data in some way?

Bill Manville
MVP - Microsoft Excel, Oxford, England

 

[Guest]

Do you need all the data to be retrieved, or could you make the source of the
data be a query which summarises the data in some way?

Bill Manville
MVP - Microsoft Excel, Oxford, England

I do need all the data. Their are about 15 fields of data I need in the
pivot table.

rgds
David

 

[Bill Manville]

Maybe I wasn't clear.

Supposing your data was sales data and you wanted to be able to analyse
Revenue by any of Customer, Region, Country, Product Type, SalesPerson
and Period.

If your query is giving you one row of data for each item sold then you
could reduce the amount of data retrieved into Excel by having the
query do

SELECT SUM(Quantity) As Qty, SUM(ItemValue*Quantity) As Revenue,
Customer, Region, Country, ProductType, SalesPerson FROM <tables> GROUP
BY Customer, Region, Country, ProductType, SalesPerson

and this will combine together some of the detail records with no loss
of functionality in what you can do within the pivot table.

However, if you really do need such a massive amount of data to be held
in the PivotCache in Excel and Excel won't do it I guess you will need
to look into a different approach - e.g. using an OLAP system (about
which I know little more than the name).

Bill Manville
MVP - Microsoft Excel, Oxford, England