Append query, new records only

  • Thread starter Thread starter Sherriff
  • Start date Start date
S

Sherriff

Hi, I am fairly new to using access so forgive me if this is a simple
question: I have a table that records are being added to daily
(Table1) and then deleted days later, I want to have another table
(Table2) as a backup that will store all records that have been added
to (Table1). So my append query will take only the new records and
append them to my backup table. When I just do an append query, it
wants to take everything from (Table1) and dump it inot (Table2) and I
get an error. How can I get the query to only add new records?

Thanks

Sherriff
 
Dear Sherriff:

You would have to either mark the records already added to Table2 or
keep track of a date-time each record was added to Table1 plus the
date-time through which they have already been added. You must have a
positive, reliable way of tracking if you want to do it the way you
suggest.

I believe a much better way of meeting your requirements is possible.
Use only 1 table, not 2. Have a query criteria that shows the
date-time of the cutoff between archived and live records within that
one table. Thus, the division becomes one within the one table
instead of between two tables.

There are possible reasons why this would not be a good design. For
one, if there is an absolute need to separate the data, such as
putting it on a different computer or on separate media. Also, it
might be that the data in Table2 might get really huge, like hundreds
of gigabytes. However, with Jet or MSDE (the databases provided with
Access) there is a 2 gigabyte limit, so the latter cannot be the case
unless you design for MSDE and migrate to a full SQL Server.

Otherwise, I really recommend you just keep the one table, and divide
it logically.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Add a new field to table 1 "BackedUp" either Yes/No or Date Stamp

Run two queries. First Copies records WHERE BackedUP is False (or
null)
Second sets BackedUp to True (Or Date)
using DateStamp allows you to redo a batch of records that were done
previously.

This will not help you if the records are re entered into Table1 as
these will be new records any way. you will need to compare the tables
and look for duplicates in this case. It becomes a more difficult
issue.

HTH GregK
 
Back
Top