How do we historicize some rows in data tables? (6 Viewers)

[amorosik]

Suppose we have our beautiful program that operates on three tables: Documents, Customers, and Phone Calls
After several years, the Documents table will contain many records that will likely no longer be useful and will unnecessarily burden the archive
How do we historicize all documents dated before 2025 in the DOCUMENTS table and then physically move them to another database, while still being able to retrieve the historicized information?

 

[GPGeorge]

An append query, followed by a delete query, both with appropriate parameters, would do the job. You can retain access to the archived data by linking it to the production database.

I am not sure, though, what you mean by "historicize" them. How is that different from moving them to the archive database?

 

[KitaYama]

Why do you need to move them to another database? Data doesn't take too much space on disk. You can set a default value for date in your search form, so when users search for something, it shows the data from 2025 and later as default. One bonus is that if for some rare reasons they need to refer to old data, they simply can change the default value and search.

 

[DickyP]

My solution has been to save the historic data in the tables to XML, which is easily retrievable, when required.