Append Query question - Is this scenario possible?

[kcon]

I need to create an append query to archive a block of records. The goals for
this record archive are:

1.) To get them out of the main database (done by a delete query later after
append?)
2.) To have them accessible and available to be referenced at a later date.

I have a major issue. Do append queries have to be run ONLY on tables? The
listing of records to be archived in question was created from a query AND a
table. Is it possible to run an append query in this scenario?

Another question: Do I create a blank table with all of the values to accept
the appended records? how is this done?

For the listing I made, the query categories were: Category Name, First
Name, last Name, Category Abbreviation and Status. Table categories were:
Elder Category, Category Abbreviation and Appearance Order. I ran these two
together as a query. To make the list, the record had to have a status of
'inactive' and a category abbreviation of one of two values.

I am using MS Access 2003. I am not an Access or SQL expert by any means. I
am teaching myself through help screens and various internet forums. Any help
would be greatly appreciated!

 

[tina]

usually, when archiving table data, you dump the data into an identical
table - usually in another database, though not always. but there are a
couple considerations: 1) do the targeted records have dependent records in
one or more additional tables? if so, you'll ned to archive those child
records as well. archive the parent records, then archive the child records.
then, if CascadeDelete is turned on in the table Relationships, you can just
delete the original parent records, and the original child records will be
removed automatically. otherwise, you must delete the original child records
first, then delete the original parent records. and 2) making the archived
records available again can be a pita. you'll either need a separate set of
forms/reports bound to the archive tables, or use one set of forms/reports
and give the user a way to change the objects' RecordSources from the
current tables to the archive tables.

having said all that, unless you are having an issue with total database
size, i probably wouldn't bother archiving records into "storage" tables.
just leave all the records in the original table(s); you can usually filter
out obsolete records at the query level - but if that gets too involved to
be practical, you can write a query to identify records to be "archived",
and then use an Update query to, for instance, set a Yes/No "Obsolete" field
to Yes. easy to filter your queries then - to see current records, Obsolete
= False, and to see archived records, Obsolete = True.

hth