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


G

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.
 
Ad

Advertisements

B

Bill Manville

David said:
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
 
G

Guest

Bill Manville said:
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
 
Ad

Advertisements

B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top