Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

Excel pivot table keeping old data.

Posted by Dave on January 16, 2002 11:31 AM
I have an excel pivot table that queries an access 2000 database upon workbook open. It refreshes the data correctly, but keeps 'old' data...

If i delete 1000 records or so, and all records of a particular 'index' that i am sorting on are gone, the pivot table keeps that index, even though there is no actual data for it.

How can i remove these 'ghost' data items?

Thanks in advance


Create a new PivotTable (nt)

Posted by Mark W. on January 16, 2002 11:35 AM


Re: Create a new PivotTable (nt)

Posted by Dave on January 16, 2002 11:41 AM
Is it possible to 'easily' configure this to happen automatically?

I dont feel like deleting and recreating a pivot table for up to 20,000 rows of data every morning.


Re: Create a new PivotTable (nt)

Posted by Mark W. on January 16, 2002 11:54 AM
I suppose that a VBA could be written to re-create
the PivotTable. Since the "old" data doesn't
appear in the PivotTable's ROW or COLUMN areas
why do you feel the need to elimnate it?


Need to have only fresh data...

Posted by Dave on January 16, 2002 12:02 PM
I actually calculated wrong..

The database captures live data every 5 seconds (6 fields)
One of these fields is an ID number of sorts, there can be upwards of 2800 records for one ID number.

This data gets pulled from the database into an Excel report i created for management.

A Pivot table refreshes on startup (I figured this would be enough), but ID numbers that have been purged (Only one day worth of records, 24 hrs) are still in the "Select the ID to view the trend data for" dropdown on the table And chart.

Deleting and recreating the pivot table every morning, for 69,000+ records would take till noon.

Takes long enough right now to just refresh.

* Thats 4 records updated every 5 seconds * 24 hrs in a day


Re: Need to have only fresh data...

Posted by Mark W. on January 16, 2002 12:08 PM
If your ID numbers are serialized maybe it would
be easier to create a Grouped ID field where all
the "old" IDs (prior to a given ID number) are
grouped as "Others" or "Obsolete".


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.