Append 13th month of data & remove 1st to remote dbase?

[Screwtape]

I have a monster file in which I would like to keep the latest 12 months of data.

Problem is, my working database is 100+MB, and it gets a bit unwieldy at times. On top of that, I have a great tool I created to blow all the data down into a single executive summary page...but when I create analysis pivot tables in Excel, they end up being 25+MB each. Its eating up my hard drive.

Is there a way to automate the append of the new 13th month, pull the first month off, and dump it into another database? This will keep my day-to-day database in a somewhat manageable size, maintains my 12 month rolling window, while allowing me maintain the old records in case I need to do a historical pull.

Anyone able to tackle this, or am I requesting the un-possible ? I wish I was remotely smart enough to figure this out...but I've recently purchased 3 of Bill's books...so I'll be a self-helper in no time! :wink:

Thanks,
Screwtape

 

[Norie]

Regarding the size of the database, have you tried Tools>Database Utilities...Compact and Repair...

Why not just use queries to get the data you want?

You could use criteria that will only return the last 12 months.

 

[tonnic]

If you have a column like update you could make a query which inserts all rows where update < (current date - 13 months) into another database/table and with a second query you can delete this files.

 

[Screwtape]

Norie, compact got me down to 100Mb!

I hadn't considered modifying the underlying query. I'll try that first.

Unfortunately, this was a collaborative effort with another co-worker was is an Access maven...who is now in Ohio. Not the most effective way to collaborate when you're working with 100+Mb sized files. LOL

I'll keep you posted,
Screwtape

 

[Piglett]

13 months data

Hi

This is one of the jobs I hate. However you can do it with a formula.

Make a select query, based on your table, and include extra column where you calculate variable "TP" (This Period), place it as first column.

So TP: Format((Year(Now())+(Month(Now())/100)),"0000.00") . You should now have a field, like 2005.02. Use same coding on your date field, that you want to identify as being "moveable" etc. With this field, do data selection on <[TP]-1.

Because TP is not a whole number, the -1 subtracts from the actual year, not month, and thus you maintain a whole years data.

HTH