Deleting the wrong duplicate

[mattzkn]

OK, here is my situation (simplified)

I have two tables. 'Clients' and 'New Clients'

Table 'Clients' just has one field 'Client Name' and this table should never be edited

Table 'New Clients' has multiple fields which includes 'Client Name' and new data is added to it regularly.

I created a query with a simple inner join to show duplicates between the two tables

Sometimes this may be 20 or 30 names depending on how regularly it is checked. I want to be able to select all the names in the query and delete them, but i only want them deleted from table 'New Clients' and for the names to remain in 'Clients'. However, the exact opposite is inexplicably happening and it is only deleting the names from the table 'Client' and leaving them in 'New Clients'

Does anybody know how to solve this?

P.S....even better would be if it stopped me / told me that the name already existed in 'Clients' when someone originally enters it in the field 'Client Name' in the table 'New Client'



Thanks!

Matt

[ItsMe]

You could use DAO to loop through a recordset of table NewClients to search for and delete duplicate records. The same methodology could be applied to search Clients and avoid a duplicate being added in the first place.

The hard part is determining if a record is a duplicate or not. Names get misspelled, individuals can share names with others, nicknames can be used. In the end, it may be best to employ a search form technique to get user feedback. I don't like to depend on Users for analyzing data, but this may be an instance where it is necessary.