Need help with archiving strategy

[Tony Girgenti]

Hello.

I want to remove old records from a database to an archive. The archived database will have three tables Customers table will simply be a straight copy, PendingOrders table will be pulled based on a date from a form, Status table is linked to PendingOrders by JOBNO.

I thought about just creating a new database with the same name as the original+date&time with empty tables, doing an append query to the archived version and a delete query to the original database with form date as the criteria to archive and delete.

How do i do that with all the three tables ?

The Status table does not have a date but is linked to the PendingOrders table. How do i set up the append/delete query to use the joined tables ?

How do i create the new database with empty tables ?

How do i copy the Customer tabel to the new database ?

Or should i do this whole thing a different way ?

Thanks,
Tony

 

[Joseph Meehan]

Hello.

I want to remove old records from a database to an archive. The
archived database will have three tables Customers table will simply
be a straight copy, PendingOrders table will be pulled based on a
date from a form, Status table is linked to PendingOrders by JOBNO.

I thought about just creating a new database with the same name as
the original+date&time with empty tables, doing an append query to
the archived version and a delete query to the original database with
form date as the criteria to archive and delete.

How do i do that with all the three tables ?

The Status table does not have a date but is linked to the
PendingOrders table. How do i set up the append/delete query to use
the joined tables ?

How do i create the new database with empty tables ?

How do i copy the Customer tabel to the new database ?

Or should i do this whole thing a different way ?

Thanks,
Tony

I suggest you just add a yes-no field and call it archived. Make the
default value no and then change it to yes to archive it. Use a simple
query to display only non-archived data for your normal work.

 

[tina]

unless the database is approaching its' size limit, or there is some other
issue that cannot be solved except by physically removing records, i
probably wouldn't do it. when working with the data in queries, forms, and
reports, you can use criteria and/or filters to ignore the "old" data, based
on a date field that already exists, or by adding a Yes/No field to the
tables called Archived and pulling either False values (not archived), True
values (archived), or both, to suit the needs of the moment. this would
require periodic "housekeeping" to set the value of the Archived field to
True as records age, whereas using a date field that already has meaning in
the database probably would not require any additional handling.

btw, suggest you post in plain text rather than HTML, because not all
newsreaders can handle HTML.

hth


Hello.

I want to remove old records from a database to an archive. The archived
database will have three tables Customers table will simply be a straight
copy, PendingOrders table will be pulled based on a date from a form, Status
table is linked to PendingOrders by JOBNO.

I thought about just creating a new database with the same name as the
original+date&time with empty tables, doing an append query to the archived
version and a delete query to the original database with form date as the
criteria to archive and delete.

How do i do that with all the three tables ?

The Status table does not have a date but is linked to the PendingOrders
table. How do i set up the append/delete query to use the joined tables ?

How do i create the new database with empty tables ?

How do i copy the Customer tabel to the new database ?

Or should i do this whole thing a different way ?

Thanks,
Tony

 

[Jeff Boyce]

Tony

Just in case...

If you're wondering about the size, it would depend on how large your db is,
and how fast it is growing. If your database is under 100 Mbytes, or
wouldn't likely grow that large in the next 10 years or so, it probably
isn't an issue.

If performance is a concern, you can set indexes in the tables to ensure
better performance.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hello.

I want to remove old records from a database to an archive. The archived
database will have three tables Customers table will simply be a straight
copy, PendingOrders table will be pulled based on a date from a form, Status
table is linked to PendingOrders by JOBNO.

I thought about just creating a new database with the same name as the
original+date&time with empty tables, doing an append query to the archived
version and a delete query to the original database with form date as the
criteria to archive and delete.

How do i do that with all the three tables ?

The Status table does not have a date but is linked to the PendingOrders
table. How do i set up the append/delete query to use the joined tables ?

How do i create the new database with empty tables ?

How do i copy the Customer tabel to the new database ?

Or should i do this whole thing a different way ?

Thanks,
Tony