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
Posted by Mark W. on January 16, 2002 11:35 AM
Create a new PivotTable (nt)
Posted by Dave on January 16, 2002 11:41 AM
Re: Create a new PivotTable (nt)
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.
Posted by Mark W. on January 16, 2002 11:54 AM
Re: Create a new PivotTable (nt)
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?
Posted by Dave on January 16, 2002 12:02 PM
Need to have only fresh data...
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
Posted by Mark W. on January 16, 2002 12:08 PM
Re: Need to have only fresh data...
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".
