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.

 

[Ken Sheridan]

As KitaYama has pointed out, there is no need to move the rows to another table. The value of an Historicized column of Boolean (Yes/No) data type could be set to True. In its normal day to day operations the database would then use those rows WHERE NOT Historicized.

The attached little demo file illustrates a similar approach to mark records as deleted. One or more records marked as deleted can be restored at any time should this be necessary. You'll see that the demo includes an option to delete multiple rows where the value in a column of date/time data type is prior to a date entered into an unbound combo box in a dialogue form. This is analogous to marking rows as historicized in your case.

The demo does include an option to move a single deleted row to a backup table, as you are proposing. You could easily amend this to move multiple rows. One or more rows can be restored from the backup table at any time if necessary. Note that to avoid primary key conflicts if the database is compacted and repaired, and a backed up record is then restored, the ContactID primary key of the Contacts table in the demo is not an autonumber, but is computed when a row is inserted.