Append / Delete Query Question

[Ryan]

Hi,

I have a table where periodically records are deleted (i.e., bc a case has
been terminated). These records, however, need to be put into an 'archive'
table. I am currently accomplishing this by first running an append query
and then a delete query. Is there a way to combine the two actions?

Also, is there a way to design an append query that appends from two related
tables (one to many relationship) to a like set of tables?

Thanks in advance,
Ryan

 

[John Spencer]

The answers to your questions are
No, you cannot combine the append and delete queries into one. You could create
VBA code to call the two queries sequentially. You would want to be sure that
the first query was successful, before you ran the second.

As far as I know you can only append to one table at a time. You can append
from two related tables into one table, but not from two tables to two tables in
one query.

Normally, a better way to handle this situation is to keep the records in the
table and just use another field in the table to mark the records as "archived"
using a boolean field or a datetime field if you want to know when the data was
archived or a text field if you want to store the reason for archiving. Then
you used the "archive" field to screen out archived records when you don't want
them in your data.

Of course, in a database that has been around for a bit and has lots of forms,
queries, and reports already built,this may not be easy to implement.