+ Reply to Thread
Results 1 to 11 of 11

Access VBA or SQL to find and delete duplicate records in access table

  1. #1
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Access VBA or SQL to find and delete duplicate records in access table

    Hi,

    I need help in creating a VBA or SQL to find and delete duplicate records in an access table.

    Thanks

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Access VBA or SQL to find and delete duplicate records in access table

    To delete duplicate record you can probably do

    Please Login or Register  to view this content.
    OR you can use Insert Into to create a unique list table.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to find and delete duplicate records in access table

    Hi,

    i am using the first code but it's not working. it says Syntax error in FROM clause. Below is the code that I have now. Thanks

    Please Login or Register  to view this content.
    Quote Originally Posted by JieJenn View Post
    To delete duplicate record you can probably do

    Please Login or Register  to view this content.
    OR you can use Insert Into to create a unique list table.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Access VBA or SQL to find and delete duplicate records in access table

    Field is the name of the column. What I posted was just what your SQL statement syntax will look like.

  5. #5
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to find and delete duplicate records in access table

    so FieldX is the name of my column too?

  6. #6
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Access VBA or SQL to find and delete duplicate records in access table

    Yeah. It was a typo. FieldX = Field.

  7. #7
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to find and delete duplicate records in access table

    I tried to run this but it says "Could not delete from specified tables."

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Access VBA or SQL to find and delete duplicate records in access table

    Can you post a sample database. It might be easier that way.

  9. #9
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to find and delete duplicate records in access table

    I wish I could but i'd rather not do that

  10. #10
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to find and delete duplicate records in access table

    I got this error when I tried to run the code: "Error 3306. You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."


    Quote Originally Posted by JieJenn View Post
    Can you post a sample database. It might be easier that way.

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Access VBA or SQL to find and delete duplicate records in access table

    Without posting your db or at least the db with sample (dummy) data, it is like shooting darts blindfolded after being spun in circles. We are only guessing what the issues are. If you really want to get help, then help us to help you by posting your db so we can see all the issues.

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1