Append, but no duplicates...

[Rjc8513]

Love these forums!

I download payroll data from a mainframe every two weeks and store it in a table called "tbl PAYROLL". I want to append this data every two weeks to a table called
"tbl ARCHIVE" so that I can have the whole year's data available. But, I want to prevent duplicate records.

I am trying to use the Field called "YRPP" (Year and Pay Period), which is common to both the downloaded file/table and the archive table. I want to test to make sure that the value of "YRPP" in the "tbl PAYROLL" does not already exist in the table "tbl ARCHIVE".

I was trying to use FindFirst to compare the two "YRPP" fields but it doesn't work correctly. I have successfully appended using the code "INSERT INTO..."

Thanks.

Richard...

 

[Databaseguy]

Figure out which field, or combination of fields, will be unique to each individual record. Then set them (or it) as your primary key(s) in the underlying table. When you run the append query, Access will tell you "Access added x records and didn't add y records because of key violations" This is what you want. What Access is telling you is that it didn't add the duplicates.. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy