INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

Delete Duplicates (Save One From Each Group) by TheAceMan1
Posted: 27 Feb 05 (Edited 19 Sep 06)

To All . . . . .

A full amendment of the SQL to perform this task has been made. Following are the steps to install this powerful SQL!
  1. Copy the following SQL to a text editor so you can see better (WordPad would be great as you can maintain color of the tags). Your going to edit the SQL, and its important you maintain spacing and brackets where you see them. One error here and it won't work!

    CODE

    DELETE [DF]
    FROM [TN]
    WHERE [ID] Not In (SELECT Max([ID]) FROM [TN] GROUP BY [DF]);
  2. In the SQL you'll see three tags:
    1. DF is YourDuplicateFieldName
    2. ID is YourIDFieldName (usually the primarykey)
    3. TN is YourTableName
    Parse thru the SQL and replace the tags with the proper names.
  3. When your sure your done, open a new query in design view (do not select any tables) and copy/paste the SQL into SQL View (be sure to paste over the Select; that already there).
  4. Switch to DataSheet View to test its working and view the records that will be deleted. From here your ready to fly!

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close