Need help with archiving strategy


T

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
 
Ad

Advertisements

J

Joseph Meehan

Tony said:
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.
 
T

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
 
Ad

Advertisements

J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form/subform strategy help needed. 1
Any Ideas? Need strategy 1
Best strategy for this? 1
Strategy for a databse 5
Best strategy? 4
Strategies for "play" databases? 7
Design/Strategy Question 4
Archive 2

Top