02 September 2009

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.

No comments:

Post a Comment

Feel free to write any comments or ideas!

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.

0 Comments:

Post a Comment

Feel free to write any comments or ideas!