August 4, 2018 at 2:31 pm
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!
August 4, 2018 at 4:00 pm
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!
August 4, 2018 at 4:17 pm
I wish there were an equivalent to ROW_NUMBER() in Access. Sooo handy in SQL Server! Thanks!
August 5, 2018 at 7:20 am
pietlinden - Saturday, August 4, 2018 4:17 PMI 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.
😎
August 5, 2018 at 9:10 am
Thanks Eirikur. I considered that... just seems there really isn't a good lightweight way in Access. =(
August 5, 2018 at 9:27 am
pietlinden - Sunday, August 5, 2018 9:10 AMThanks 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
August 5, 2018 at 10:04 am
Eirikur Eiriksson - Sunday, August 5, 2018 9:27 AMAccess 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!
August 5, 2018 at 10:22 am
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.
August 5, 2018 at 10:45 am
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!
August 5, 2018 at 10:49 am
I learned Access first, and the whole mutant query engine/VBA thing was just weird.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply