Append Query behaves erratic

[albert8426]

I am using an Append Query in Access 2002 that copies 1 record at a time from a (static) master table to a secondary table.

The secondary table may contain multiple copies of entire records of the master table with some data in additional fields

The primary key for the secondary table is an added auto number field

The tables are joined with the primary key for the master table.

This Query works well for days at a time, and does just what it is supposed to do, namely add one record every time it is executed.

Occasionally, after execution of the query, MS Access displays a variety of error messages, and either hangs, or shuts itself down.

When we re-open the database after such an error, this particular append Query does not just add one copy of the record at a time anymore, but it adds one copy for every copy of the record that had previously been added to the secondary table.

Short of restoring the entire database from a backup, I am unable to get the query to function properly after Access did hang

Can anybody give me any hints, as to what I could have possibly done wrong.

Thank You so much in advance.

Albert

 

[Morgance]

Sounds like a join problem. If the record already exists, it will append 1 record for every match it found. If it doesnt exist works fine, adds 1. If it already exists, you'll get 1X1, then 1X2, then 1X4, follow??? If you want all records added regarless of if it already exists, just append without a join. Insert into [Table 2] select * from [Table 1]. If you don't want duplicates, delete where the records match first and then append the new record.

Morgance