02 September 2009
Microsoft Access: SQL COUNT DISTINCT Alternative
Yeah I did not mistype. I have been working with Microsoft Access since Microsoft Access 2000 and I never ran into this problem before! Weird but expected since I attempt to avoid Microsoft Access whenever I could and only use it for those quick small projects or tests.
Anyway, you might be wondering what's the workaround or alternative. It's quite simple but annoying:
Standard SQL: SELECT COUNT(DISTINCT NAMES) FROM USER_NAMES
Microsoft Access: SELECT COUNT(*) FROM (SELECT DISTINCT NAMES FROM USER_NAMES)
God I love Microsoft. See how easy it is in comparison to standard SQL? Of course for bigger queries, the idea gets a little bit more complex but you get the general idea.
Microsoft Access: SQL COUNT DISTINCT Alternative
I was kind of taken by surprise yesterday when I was working on a small project of mine and realized that Microsoft Access does NOT support COUNT(DISTINCT)!
Yeah I did not mistype. I have been working with Microsoft Access since Microsoft Access 2000 and I never ran into this problem before! Weird but expected since I attempt to avoid Microsoft Access whenever I could and only use it for those quick small projects or tests.
Anyway, you might be wondering what's the workaround or alternative. It's quite simple but annoying:
Standard SQL: SELECT COUNT(DISTINCT NAMES) FROM USER_NAMES
Microsoft Access: SELECT COUNT(*) FROM (SELECT DISTINCT NAMES FROM USER_NAMES)
God I love Microsoft. See how easy it is in comparison to standard SQL? Of course for bigger queries, the idea gets a little bit more complex but you get the general idea.
Posted by Ahmed Elgarhy at 9:47 AM
Labels: Microsoft Access
No comments:
Post a Comment
Feel free to write any comments or ideas!