Remove Duplicates from Table

[natalie99]

hi everyone

i have reseached this topic and cannot seem to find a solution that suits my problem, please help!!

I have two tables, "Inventory" which contains 30,000 or so records, with about 2000 duplicates. Each duplicate contains the exact same data in every field except one, Field "Code", which may have "Agreed" or "Rejected".

The second table, "Duplicate Coding" contains the correct field value for "Code" for the records which are duplicates. This table was created by using Find Duplicates and then running a query to negate the incorrect Code values.

Now I need to merge the data, I wish to delelte all duplicate lines where the Code value from Duplicate Coding does not match, WITHOUT affecting the other lines in table Inventory.

The two tables both contain an ID number to reference records.

I hope this makes sense? I know there will be a simple solution but I can't seem to make it work!

Thanks in advance!

[ADezii]

Kindly post some data to visually illustrate your request.

[puppydogbuddy]

update tblInventory
    set tblInventory.Code = tblDuplicates.Code
    where tblInventory.ID = tblDuplicates.ID

[NeoPa]

Try the following query. Let me know if it complains about using a non-updatable query to delete.

DELETE
FROM tblInventory AS tI INNER JOIN
     [Duplicate Coding] AS tDC
  ON tI.ID=tDC.ID
 AND tI.Code<>tDC.Code

[NeoPa]

PS. Please remember to title your questions correctly in future ;)

[natalie99]

Thank you everyone :)

I ended up using this format:

DELETE
FROM tblInventory AS tI INNER JOIN
     [Duplicate Coding] AS tDC
  ON tI.ID=tDC.ID
 AND tI.Code<>tDC.Code

with a small change, as this exact query timed out every time I tried to run it. Thank you NeoPa for helping me fix my shoddy data!!

Cheers

Nat

[NeoPa]

No worries Nat.

I'd be interested to see exactly what SQL code you did use mind you, to see where in my code I could have done it differently perhaps.

[natalie99]

:S

So very sorry I should have stated that correctly, changes were only due to naming conventions, not coding issues!

thanks for your help (again!)

[NeoPa]

You're sorted now Nat, that's the important thing :)