move records table to table

I have 2 tables with the same fields. How can I move the
records from one table to the other?

write a Select query to pull the correct records from Table1. change the
query to an Append query, to append the records to Table2. make a copy of
the Append query, and change the copy to a Delete query to delete the
records from Table1. run the Append query, and then the Delete query.
look up Append and Delete queries in Access Help, for details on same.
NOTE: test the queries on a *copy* of your database before using them on
"live" data.

hth


"Chuck H" wrote in message
...
I have 2 tables with the same fields. How can I move the
records from one table to the other?

"Chuck H" wrote in
:

I have 2 tables with the same fields. How can I move the
records from one table to the other?

Why would you want to? There is hardly ever any good reason to have two
tables with the same field structure. It usually comes about as a
misunderstanding of design basics; often one table is "ActiveCases" and the
other is "DeletedCases". This is better modelled by having one table with
all the cases, and adding a field called "Active" which can be set to True
or False. This leads to a _huge_ saving of effort down the line, not least
in terms of getting rid of duplicates (what happens when a case is in both
the Active and Deleted tables?), reporting on All Cases (yes, you can use a
Union query but that introduces all kinds of side effects), updating the
application (adding fields to two tables instead of one, doubling up all
the validation code, etc), and so on.

Oh: how to move a record from one to the other? Change the field to
"False".

Hope that helps


Tim F

Sound database design would dictate as Tim describes for relatively small
databases. For larger databases (thousands of records) you could build a
function to periodically archive older records to text files (csv, etc) for
later retrival, if desired.

Judi B

"Tim Ferguson" wrote:

"Chuck H" wrote in
:

I have 2 tables with the same fields. How can I move the
records from one table to the other?

Why would you want to? There is hardly ever any good reason to have two
tables with the same field structure. It usually comes about as a
misunderstanding of design basics; often one table is "ActiveCases" and the
other is "DeletedCases". This is better modelled by having one table with
all the cases, and adding a field called "Active" which can be set to True
or False. This leads to a _huge_ saving of effort down the line, not least
in terms of getting rid of duplicates (what happens when a case is in both
the Active and Deleted tables?), reporting on All Cases (yes, you can use a
Union query but that introduces all kinds of side effects), updating the
application (adding fields to two tables instead of one, doubling up all
the validation code, etc), and so on.

Oh: how to move a record from one to the other? Change the field to
"False".

Hope that helps


Tim F