Remove duplicates Access

  • pietlinden

    SSC Guru

    Points: 62342

    In SQL Server this is much easier because I can use Window functions and ROW_NUMBER()... Is there an easy way to remove duplicates in Access without resorting to VBA? I can do that, but seems like I shouldn't have to do that. Sorry I don't have a specific example (Yes, I know I should have one!)
    Thanks!

  • WendellB

    SSCrazy Eights

    Points: 8590

    It's quite easy to create a query that will identify duplicate records in Access, but deleting them is a more complicated process.

    Take a look at How To Delete Duplicate Records ... The instructions are essentially correct except for one minor point noted in the replies to the thread.

    Post back if that doesn't do the trick.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • pietlinden

    SSC Guru

    Points: 62342

    I wish there were an equivalent to ROW_NUMBER() in Access. Sooo handy in SQL Server!  Thanks!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    pietlinden - Saturday, August 4, 2018 4:17 PM

    I wish there were an equivalent to ROW_NUMBER() in Access. Sooo handy in SQL Server!  Thanks!

    You can select distinct into a new table, probably the easiest option, another option is a relatively simple VBA function which counts the number of identical entries and enumerates them, not the best of performers but work similarly to the row_number function.
    😎

  • pietlinden

    SSC Guru

    Points: 62342

    Thanks Eirikur. I considered that... just seems there really isn't a good lightweight way in Access. =(

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    pietlinden - Sunday, August 5, 2018 9:10 AM

    Thanks Eirikur. I considered that... just seems there really isn't a good lightweight way in Access. =(

    Access has always been a different thing/language from the SQL Server, just looking at the Access function's gategories makes this obvious. Some the features would be lovely to have in SQL Server but my guess is that many of those would have a hevty performance impact.
    😎

    The list of Access function categories has no analytical functions:crazy: 

    ActiveX
    Application
    Arrays
    Conversion
    Database
    Date/Time
    Domain Aggregate
    Error Handling
    File Input/Output
    File Management
    Financial
    Inspection
    Math
    Messages
    Miscellaneous
    Program Flow
    Text

  • WendellB

    SSCrazy Eights

    Points: 8590

    Eirikur Eiriksson - Sunday, August 5, 2018 9:27 AM

    Access has always been a different thing/language from the SQL Server, just looking at the Access function's gategories makes this obvious. Some the features would be lovely to have in SQL Server but my guess is that many of those would have a hevty performance impact.
    😎

    For that and other reasons, we've always chosen to use Access for "front-end" purposes and SQL Server as the back-end.  You get the best of both worlds!  😀

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • pietlinden

    SSC Guru

    Points: 62342

    Amen to that. I think the querying capability in Access is meh on a good day. I was asking originally because I was looking at a job posting where I would have to query in Access all day. My vote would be to write stored procedures in SQL Server and write pass-throughs.

  • WendellB

    SSCrazy Eights

    Points: 8590

    Well, it depends on your situation.  We started with SQL Server 4.7 and Access 2.0, and at that point the query tools in Access were much more friendly than the tools for creating sprocs.  Over time that has changed with both products, but for simple queries, Access is good quick tool with the query designer.  However it isn't likely to be as efficient as a query in SQL Server.  We found that an excellent tool was to create indexed views in SQL Server, so that the ODBC driver converted the Access Queries to SQL syntax, and delivered excellent performance.  Truth be told, I think it depends on which tool you learned first.  We learned both at the same time, and have a foot in both camps.  :unsure:

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • pietlinden

    SSC Guru

    Points: 62342

    I learned Access first, and the whole mutant query engine/VBA thing was just weird.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply