Find and delete duplicates

[Khalil Handal]

Hi,
I have three tables in a database with many to many relationship.

tblBooks:
pkBookId, Booktitle, ISBNNumber...... other fields

trelBooksKeywords:
pkBooksWords, fkKeyWords, fkBookId

tblKeyWords:
pkKeyWords, KeyWords

I already have around two hundred records.



Table tblKeyWords has a lot of duplicates in it.
How can I delete all these duplicates so as to have the Indexed property of the KeyWords field being set to: Indexed (duplicates NO)

Khalil

[Bob Fitz]

Perhaps you need to create a find duplicates query. See: https://www.gcflearnfree.org/access2...cates-query/1/

And then delete the duplicate. BACK UP YOUR DATA FIRST

[orange]

Khalil,
What exactly do you consider a duplicate? Please give us an example or 2?
Can you show us some records from tblKeywords?
What exactly do you store in field Keywords?

[Khalil Handal]

Khalil,
What exactly do you consider a duplicate? Please give us an example or 2?
Can you show us some records from tblKeywords?
What exactly do you store in field Keywords?

The table tblKeywords is a table having words or phrases and each word / phrase is supposed to be mentioned only once and there is no need to have them repeated in different rows.
My mistake from the beginning was that these words were entered as text fields instead of having them as combo boxes where one can select the word (if it does not exist one can add it).
What I am seeking is to remove the repetitions.
Attached is a sample showing it.

Khalil

[CJ_London]

use a query something like

DELETE *
FROM tblKeyWords WHERE pkKeyWords NOT IN (SELECT MIN(pkKeyWords) FROM tblKeyWords T WHERE KeyWords=tblKeyWords.KeyWords)

Make sure you take a copy of the table first in case you do not get the required result.

Actually that does not go far enough - before deleting the duplicate records, you need to update the trelBooksKeyWords table to only use the first instance

make this translation query

SELECT O.pkKeyWords AS OldPK, N.pkKeyWords AS NewPK
FROM tblKeyWords O INNER JOIN tblKeyWords N ON O.KeyWords=N.KeyWords
WHERE O.pkKeyWords<>N.pkKeyWords

In you example this should produce a result of

OldPK...NewPK
220......195

then create an update query

UPDATE trelBooksKeyWords SET pkKeyWords=NewPK
FROM trelBooksKeyWords INNER JOIN TranslationQuery ON trelBooksKeyWords.pkKeyWords=TranslationQuery.OldPK

[orange]

What do you do with misspellings or plural forms etc?
eg Conflict vs conflicts?

le comunità cristiane vs comunità cristiana

[Khalil Handal]

Thanks to all.
As for the conflicts, some one will revise it.