That would then delete all rows with that ClientName including the non duplicates for other FormNumbers would it not? ie it would delete data which whilst a duplicate is not considered a duplicate for this purpose?
What I have to work out is a way of deleting duplicates only if the FormNumber, JobReference and ClientName match another record...
I cant find away to not have duplicates, basically a search is undertaken for a new client against an existing client list which returns a list of clients. These need to be saved and so are appended to a table and then vba code ads in the primary key for the new client (new client and existing client information is in two separate tables).
I therefore have a new table with the information in and as shown in table earlier in thread.
But there is then a way for the user to undertake a new search with new information as the project continues ie continually updating the search and this adds in the duplicates.... I therefore want to include in the VBA a duplicate check and either remove or a way to not add this information if there are duplicates.
Still havent got round to this as im putting it off and tackling other issues in the database which Im coming up against mainly automatic emails!
Why do you have 2 tables? There should only be one table of clients whether they are new or not or even prospective clients.
The primary key for the client table should then be used as a foreign key in all other tables.
Normalized tables are the foundation of any database. Unless you have a good foundation anything else you try to do will be wrought with problems.
You should probably study up on DB normalization before doing anything else.
If you want to post a copy of your DB or a snapshot of your relationships I'm sure you'll get lots of help.