tag:blogger.com,1999:blog-8134759511126815909.post778479573338092509..comments2023-08-20T04:10:01.095-04:00Comments on <a href="http://rogersaccessblog.blogspot.com/">Roger's Access Blog</a>: COUNT DISTINCT In Access: Part 3Roger Carlsonhttp://www.blogger.com/profile/12265719129831415014noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-8134759511126815909.post-23699937287620695442012-12-17T16:52:30.103-05:002012-12-17T16:52:30.103-05:00I'm still amazed that a function like this doe...I'm still amazed that a function like this doesn't already exist in Access. <br /><br />I continually run into the need for it specifically in reports.<br /><br />Example:<br /><br />I'm currently working on a report (whose data already comes through several different queries, including a rather large union query), where I have the individual records grouped by State (more specifically the field DepartState).<br /><br />In the group header, I'd like to add a a counter for the number of individual counties that exists in the set of records displayed (specifically DepartCounty). <br /><br />(I don't actually show the detail lines here, just the group header, which displays a line for each state).<br /><br />I THINK the user-defined function is the way to go, but I'm not entirely sure.Dorvhttps://www.blogger.com/profile/00823230455073117578noreply@blogger.comtag:blogger.com,1999:blog-8134759511126815909.post-58156037564347741792012-12-04T17:38:07.470-05:002012-12-04T17:38:07.470-05:00Very interesting comparison, thanks !
If you still...Very interesting comparison, thanks !<br />If you still have the tables that you prepared, it would be interesting to benchmark again, but instead of calculating the time to do an OpenQuery, calculating the time of <br />a) opening the recordset b) do a rs.movelast<br />Patrick Honorezhttps://www.blogger.com/profile/01597068337034070277noreply@blogger.comtag:blogger.com,1999:blog-8134759511126815909.post-13061817877096759652012-12-04T14:22:39.313-05:002012-12-04T14:22:39.313-05:00Thank for posting the code for the user defined fu...Thank for posting the code for the user defined function. Note: the sql string has "Customer" instead of "FieldName".Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8134759511126815909.post-12409908743062150502012-06-08T06:27:06.663-04:002012-06-08T06:27:06.663-04:00@Patrick
Thanks for the suggestion. That's a ...@Patrick<br /><br />Thanks for the suggestion. That's a new one to me, and I've incorporated it into the series. However, you might be surprised by the performance comparison in Part 5.Roger Carlsonhttps://www.blogger.com/profile/12265719129831415014noreply@blogger.comtag:blogger.com,1999:blog-8134759511126815909.post-15565770903226214112012-05-30T04:46:15.423-04:002012-05-30T04:46:15.423-04:00You can get that Count Distinct MUCH more efficien...You can get that Count Distinct MUCH more efficiently by using a Crosstab query: <br /><br /><br />TRANSFORM Count(*) AS Cell<br />SELECT Count(cell) AS DistinctCount<br />FROM Orders<br />GROUP BY "Anything"<br />PIVOT CustId In (null);Patrick Honorezhttps://www.blogger.com/profile/01597068337034070277noreply@blogger.com