Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete Duplicates (Save One From Each Group)

How To

Delete Duplicates (Save One From Each Group)

by  TheAceMan1  Posted    (Edited  )
To All . . . . .

A full amendment of the [blue]SQL[/blue] to perform this task has been made. Following are the steps to install this [blue]powerful[/blue] SQL!
[ol][li]Copy the following [blue]SQL[/blue] to a text editor so you can see better (WordPad would be great as you can maintain color of the tags). [blue]Your going to edit the SQL, and its important you maintain spacing and brackets where you see them.[/blue] [purple]One error here and it won't work![/purple]
Code: 
[blue]DELETE [[purple][b]DF[/b][/purple]]
FROM [[purple][b]TN[/b][/purple]]
WHERE [[purple][b]ID[/b][/purple]] Not In (SELECT Max([[purple][b]ID[/b][/purple]]) FROM [[purple][b]TN[/b][/purple]] GROUP BY [[purple][b]DF[/b][/purple]]);[/blue]
[/li]
[li]In the SQL you'll see three tags:
[ol a][li][purple]DF[/purple] is YourDuplicateFieldName[/li]
[li][purple]ID[/purple] is YourIDFieldName (usually the primarykey)[/li]
[li][purple]TN[/purple] is YourTableName[/li][/ol]
Parse thru the SQL and replace the tags with the proper names.[/li]
[li]When your sure your done, open a new query [blue]in design view[/blue] (do not select any tables) and copy/paste the SQL into [blue]SQL View[/blue] (be sure to paste over the [blue]Select;[/blue] that already there).[/li]
[li]Switch to [blue]DataSheet View[/blue] to test its working and view the records that will be deleted. [green]From here your ready to fly![/green][/li][/ol]
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top