using the append query to update a table where values are different

[damngroundhog]

Hello New to access and the forum. have had wonderful guidance so far and am looking for some expert opinions. I have a table which i need to update once a week. I get my updates from an excel spreadsheet. I upload that spreadsheet into a new table. Now i have 2 tables, my main table and my spreadsheet table. I am seeking to add only the new items from the spreadsheet. The workID's are the unique identifiers and I don't want duplicates. In the query window, i created the relationship with the two tables and the workIDs. I used an append query and in the criteria i used the build function and set those to be not equal to each other "" looks like this [main]![WorkID][sheet1]![workID] it returns 0 rows. If i remove that criteria, i would get the number of rows where the ID's are equal. Any advice is duly appreciated. Groundhog

[Dave14867]

Groundhog,

it sounds to me that you may want to look at an Append query. Are all of the items in the new table actually new or does it have items previously imported?

[damngroundhog]

This is an append query.

The new table, which comes from a spread sheet, has new and older items. I don't want the older items only the new items.

this is what my criteria looked like in the append query under the workID

[main]![workid] <> [sheet1]![workid]

[Dave14867]

Sorry, I misread the original post, when I saw update I thought you were using an update query. Before you import the xl file, can you delete the older data? That way any data in table 2 would be new data and it would be easier for you to just append?

What are you using as your primary key on the import? Are you letting access add the primary key?

Edit: If you can post a zipped copy of the database I should be able to assist.

[Micron]

There is a simple way if you can use code and the target table is set to not allow duplicates. You simply run the append and rather than get the standard message that n records can't be processed, you suppress the warning. Whatever is new gets appended. Whatever is not new is bypassed. The warning can be suppressed by turning them on and off if using RunSql method (not my favourite thing to do) or CurrentDb.Execute without using dbFailOnError option.