Appending records to a table

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hello. I have two tables set up. One table holds the
full list of records. The second table holds records
until they can be appended to the first table. The first
table cannot have any duplicates.

What I am trying to do is append the records from the
second table to the first table one at a time. If the
record already exists in the first table, i want it to
stay in the second table and if the record does not exist
in the first table, i was it appended to the first table
and then deleted from the second table. This way i can
look at the second table and see what records were not
added.

Can somebody tell me how to do this? I have the idexed
field set to no duplicates, but how to add one record at
a time is where i get stuck. Any help would be great!

Thanks,
Brian
 
rather than handle each record in Table2 separately, you can process all the
records at once.
add a Yes/No field to Table2, i'll call it NoMatch.
use the query wizard to create an Unmatched Records query, that pulls all
records from Table2 that have no matching record in Table1.
turn that query into an Update query, and set the value of the NoMatch field
to True.
create a Select query to pull all records in Table2 where NoMatch = True.
turn that query into an Append query, to append the records to Table1.
make a copy of the Append query, and turn the copy into a Delete query, to
delete all records that were appended to Table1.
once you've run the Update query, Append query, and Delete query, the
records left in Table2 are the records that originally matched records in
Table1.
*note*: test this on a COPY of your database, before using it on "live"
data.

hth
 
Back
Top