Best way to append and update records

[saudkazia]

I have a list of products that need updating as well as new products to be added. Taking Northwind Traders Database as an example of products table, what is the best way to A) find that a product name is the same in my dummy table (pasted from Excel after ensuring that all data fields have matches) and edit the existing records with new data from other fields (list price, billing price) as well as add products that are not in the existing table so there are no duplicates.



The product name is calculated by the concatenation of three fields (category - volume - flavor) so the product name will be the same as long as these three fields match. No product name duplicate can exist)

I need to somehow make sure the product name calculated from the query is not already in the products table so as to get unique records to add. And another query must update the existing records with any details that are different.

Whats the best way to go about this. I don't mind multiple queries or even a form based solution (in fact this would be the ideal one)

[June7]

Options:

1. set the 3 fields as compound index and Access will not allow duplicates - it will notify user if they attempt to save record with duplicate

2. use VBA code to validate the combination is not duplicate

Users should not interact directly with tables/queries. Either option will work with a form. Just decide how much control you want to exercise and if you want to use VBA. Option 1 is simplest, Option 2 can be a bit complicated, especially because this involves 3 fields.

[saudkazia]

At the moment it will not allow duplicates. But I need to make sure that when new product list is available I can just copy and paste it and the query would sort out the difference. This will be done once or twice in a year or so.

[June7]

Why don't you do a test of the 'copy/paste' and see what would 'sort out'?