You basically create 'archive tables' that match your 'live tables' in structure, indices, and relationships. Then you step through your live table and based on a condition (date of sale perhaps or receipt date, etc.) copy that record into its archived counterpart table and delete it in the live table so it no longer exists there. Ensure you step through all child tables also and archive all related records. You should do this process on ALL tables in the system so your live tables would have all transactions from one date on and the archived tables contain all transactions prior to that. You can name the archived tables whatever you want so it makes sense when you need to use them to run reports off of them if necessary. You may even keep the same table names only store the archived tables in a separate location which can be accessed. I prefer filenames that suggest they are archive copies myself. The records are basically moved from one table to another table with a matching structure and index and any rules. That way when you need to produce reports or forms based on the archived data, you just point to the archive location instead. You can even pull queries from both the live and archive tables if you need to produce reports that contain from both sets of data.

I ran an 'archive' process on a monthly basis at night with no user intervention (for auditing purposes), but you could create a form that allows the user to do it also if you choose. In our system, every night an 'end of day' procedure was run that did certain functions and monthly an 'end of month' procedure was run that performed the archive procedure after all monthly reports were printed, verified, and the month-end accounts receivable and accounts payable were closed-out. All records had a field to flag it as 'closed-out' (processed during the month-end procedure so it no longer needed to be in the live system) and we archived based on the date AND that close-out field. If any records remained that met the date criteria but were not closed-out, the archive procedure would not run until auditing had been completed.



>Hi,
>Can you show me the "sample" ? How to store the "summary" and how the structure? Let say sales and lineitem table.
>
>Thank you