append non duplicate

[jiajian]

Hi

I am using an append query to append my data from my company ERP system (use ODBC) to a table. It had all the same field.

However, how can append only those non duplicate data to this table?

Thanks

jiajian

 

[FoFa]

If it is a lot of data, it could be slow. You could use NOT IN to select all the current KEYs in your local table, or (from the Access online Help) [NOT] EXISTS (sqlstatement) (look under subselects). I would use the NOT EXISTS syntax to check for current key values in your local table and only append those that meet that requirement.

 

[Pat Hartman]

The easiest/fastest thing to do is to delete the contents of the table and replace them entirely rather than trying to update individual records. To update your Access table would require an update query than joins to the ERP database which is probably Oracle or SQL server. Although this is possible, it is a slower method because of the heterogeneous join.

If you insist on doing this with an update query. Read the following article first.

Q127977 - ACC Updating and Appending Records in One Update Query