Moving records

[Neal Ostrander]

I have a database where we input orders on a daily basis. I need to archive
these orders for possible future use. There are two tables that are used for
the orders
1. Orders
2. OrderDetails
is there a way to move the records from these two table in to two additional
tables
1.OrdersArchive
2.OrderDetailsArchive
using only one query. There is a one to many relationship between the tables
on the PK OrderID (in orders table) FK OrderID (in OrderDetails table)
Thanks

 

[kc-mass]

You' re stuck with two queries.

 

[John W. Vinson]

I have a database where we input orders on a daily basis. I need to archive
these orders for possible future use. There are two tables that are used for
the orders
1. Orders
2. OrderDetails
is there a way to move the records from these two table in to two additional
tables
1.OrdersArchive
2.OrderDetailsArchive
using only one query. There is a one to many relationship between the tables
on the PK OrderID (in orders table) FK OrderID (in OrderDetails table)
Thanks

Are you talking about more than 250,000 records per year?

If not, consider just adding a Yes/No field to the Orders table, Archived. Set
it to Yes when you want to archive a record, No when you want to "pull it from
the archive".

Having a separate archive table in the same database file as the main table
gives you very little benefit. If you do get corruption, it's almost certain
to corrupt the whole database, not just the one table; if the table is
properly designed and indexed and your queries are optimized, you should get
satisfactory performance. The limit to a database is 2GByte, and having the
data in four tables rather than two will make you hit this limit SOONER (by a
little bit).

In short... you probably really don't need to or want to do this. You can,
with a series of Append and Delete queries and regular compaction of the
database... but you shouldn't.

 

[Petr Danes]

My two cents worth:

Copying data back and forth between tables is also slower and generates more
workspace bloat than would a simple setting of a yes/no field in a record
that is not moved.

Petr