Hi there, I am fairly new to Access and VBA and I am trying to learn reading all your interesting posts. I have a table in access with 10 fields. Only field 2 is unique. The table has more than 60000 records and I receive a new table with a few hundred records to update the original table every week. The table that i receive every week has new records to add to the original table, and records already in the original table that need to be deleted. Field 1 on the updated table includes a letter A to add or D to delete.
I am trying to find a way to compare Field 2 in both tables and to have access identifying the new records and adding them to the original table and to delete those records in the original table that were marked to delete in the updated table.
I know that is somehow confusing but i am sure that somebody has a solution for this. thanks a lot for your help.

So, to confirm... when you do the update you want to add new records and update existing records (by deleting the old version and add the new version).

Fairly simple, but you need to approach it in a piecemeal fashion, ie:

1. Remove the existing duplicated records first (this is a delete query)
2. Now all you need to do is add all the new records (append query)

1. You can identify and delete the existing duplicated records by linking on Field2 and deleting all that match, so something like:

"Delete from tblExisting where tblExisting.Field2 = tblNew.Field2"

2. Assuming you have a unique index on Field2, you can now simply add ALL records from your new table to the existing table, since you have already removed any duplicates, so something like:

"Insert into tblExisting (Field1, ... FieldN) Select Field1, ... FieldN from tblNew"

I do hope I have the right end of the stick!