{"id":12610,"date":"2022-01-04T01:29:47","date_gmt":"2022-01-04T07:29:47","guid":{"rendered":"http:\/\/www.designandexecute.com\/designs\/?p=12610"},"modified":"2022-01-04T01:30:32","modified_gmt":"2022-01-04T07:30:32","slug":"how-to-count-distinct-records-in-ms-access","status":"publish","type":"post","link":"https:\/\/www.designandexecute.com\/designs\/how-to-count-distinct-records-in-ms-access\/","title":{"rendered":"How to count distinct records in MS Access"},"content":{"rendered":"<p>Access Jet Engine does not support <strong>count(distinct xxx) syntax<\/strong>, which is supported by all major DBMS systems such as SQL Server, Oracle, MySQL, etc.<\/p>\n<p>instead you must do it like this 2 stage query of an inline view inner query fed into the aggregrate count function.<\/p>\n<pre><strong>Example: \nselect count(yourCol) as num_of_distinct_values\nfrom\n(\n    select distinct yourCol from yourTable\n)<\/strong><\/pre>\n<p>Here is a triple Nested query using the technique described.\u00a0 This will get all the leavers of a segment who was there 2 months before.<\/p>\n<p>Example:<\/p>\n<p><strong>SELECT count(cnt_cnc) , &#8220;cnt_cnc_leavers_30&#8221; as cnt_desc<\/strong><br \/>\n<strong>from<\/strong><br \/>\n<strong>(<\/strong><br \/>\n<strong>select DISTINCT [encounters &#8211; 1st Prev Month].full_nm as cnt_cnc<\/strong><br \/>\n<strong>FROM [encounters &#8211; 1st Prev Month]<\/strong><br \/>\n<strong>WHERE [encounters &#8211; 1st Prev Month].full_nm not in<\/strong><br \/>\n<strong>(<\/strong><br \/>\n<strong>select [encounter &#8211; current month].full_nm<\/strong><br \/>\n<strong>from [encounter &#8211; current month]<\/strong><br \/>\n<strong>)<\/strong><br \/>\n<strong>)<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Access Jet Engine does not support count(distinct xxx) syntax, which is supported by all major DBMS systems such as SQL Server, Oracle, MySQL, etc. instead you must do it like this 2 stage query of an inline view inner query fed into the aggregrate count function. Example: select count(yourCol) as num_of_distinct_values from ( select distinct [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":2222,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32],"tags":[],"class_list":["post-12610","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-dashboards-analytics"],"jetpack_featured_media_url":"https:\/\/www.designandexecute.com\/designs\/wp-content\/uploads\/2017\/02\/sql.png","_links":{"self":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/12610","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/comments?post=12610"}],"version-history":[{"count":2,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/12610\/revisions"}],"predecessor-version":[{"id":12612,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/12610\/revisions\/12612"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media\/2222"}],"wp:attachment":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media?parent=12610"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/categories?post=12610"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/tags?post=12610"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}