title: Creating a Custom Aggregate Function for Access Queries
body: <p>Most Common/Frequent/Dense Value</p>
  
  <p>I'd like to find (or create a custom) <strong>aggregate function</strong> for use in an MS Access query, that will return the <strong><em>most common value</em></strong> (ie, <em>"most frequently occurring item"</em>) when grouped by a different field.  Below is one example of how I would use it — but I can think of others.</p>
  
  <hr>
  
  <h1>Sample Data:</h1>
  
  <blockquote>
    <p><strong>Scenario:</strong></p>
    
    <p>I have hourly weather forecast data.  Forecasts are <code>updated</code>
    irregularly, one or more times a day, including a <code>description</code> like
    "sunny" or "cloudy" (which varies hour-by-hour).  </p>
    
    <p><strong><em>"What is the most common <code>Description</code>, per day, per forecast?</em></strong> </p>
    
    <p><sub>(You can also View or Download the sample data as an <code>.xlsx</code> from  <a href="http://jmp.sh/tI0rB0s" rel="nofollow noreferrer">here</a>).</sub></p>
  </blockquote>
  
  <hr>
  
  <p><strong>"Raw" Data (selected fields):</strong></p>
  
  <pre><code>DateTimePST        updated            description   temperature   windSpeed
  2018-02-11 02:00   2018-02-06 17:53   cloudy        -0.47         2.93
  2018-02-11 02:00   2018-02-07 18:22   cloudy        2.09          8.92
  2018-02-11 02:00   2018-02-08 22:48   rain          1.54          9.7
  2018-02-11 03:00   2018-02-06 03:00   cloudy        0.13          4.31
  2018-02-11 03:00   2018-02-06 17:53   cloudy        -0.29         2.43
  2018-02-11 03:00   2018-02-07 18:22   cloudy        1.97          5.79
  2018-02-11 03:00   2018-02-08 22:48   snow          1.4           10.2
  2018-02-11 04:00   2018-02-06 03:00   cloudy        0.16          4.07
  2018-02-11 04:00   2018-02-06 17:53   cloudy        0.02          2.78
  2018-02-11 04:00   2018-02-07 18:22   cloudy        1.89          5.54
  2018-02-11 04:00   2018-02-08 22:48   snow          1.44          10.75
  2018-02-11 05:00   2018-02-06 03:00   cloudy        0.14          3.83
  …
  …
  </code></pre>
  
  <p><strong>Grouped:</strong></p>
  
  <p>Grouping the data with a 'totals' query like this:</p>
  
  <pre><code>SELECT Int([DateTimePST]) AS DatePST, a.updated, i.description, Count(a.id) AS cnt
  FROM tblWeatherData AS a 
     LEFT JOIN tblIconsToDesc AS i 
     ON a.icon = i.icon
  GROUP BY Int([DateTimePST]), a.updated, i.description
  ORDER BY Int([DateTimePST]), a.updated, Count(a.id) DESC;
  </code></pre>
  
  <p>...produces an ordered list showing which <code>Description</code>'s are most commonly occurring:</p>
  
  <p><a href="https://i.stack.imgur.com/92Djo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/92Djo.png" alt="screenshot grouped"></a></p>
  
  <p>...but I need to list just the "Top 1" <strong>for <em>each</em> <code>DatePST</code>|<code>Updated</code>|<code>Description</code> combination</strong>, like this "desired output":</p>
  
  <p><a href="https://i.stack.imgur.com/PZzPW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PZzPW.png" alt="screenshot desired"></a></p>
  
  <p>"<strong>Ties</strong>" need to be either ignored (null or zero-length), or <em>ideally</em>, concatenated into a single value like <code>cloudy, clear</code>, which each had 12 records on that date|forecast).  </p>
  
  <p><strong>The "End Goal"</strong> is crosstab-style output like this:</p>
  
  <p><a href="https://i.stack.imgur.com/NKDUB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NKDUB.png" alt="crosstab example"></a></p>
  
  <p>This will be used in sizable datasets so manual calculation is out of the question.  I could get it done completely with VBA but it would take a ton of work, so I'm hoping that there's a way to create a <strong>custom aggregate function</strong> that can be used in the "Value" section of an Access Crosstab.</p>
  
  <p>In researching a solution, I found that SQL Server makes it pretty straightforward to add custom aggregate functions (like <a href="https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-aggregates" rel="nofollow noreferrer">this</a> or <a href="http://developmentsimplyput.blogspot.ca/2013/03/creating-sql-custom-user-defined.html" rel="nofollow noreferrer">this</a>), and T-SQL even has built-in functions that would probably do the job, like:</p>
  
  <ul>
  <li><p><a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql" rel="nofollow noreferrer"><code>DENSE_RANK</code></a>, or maybe.</p></li>
  <li><p><a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql" rel="nofollow noreferrer"><code>TOP 1 WITH TIES</code></a>,</p></li>
  </ul>
  
  <p>...so I'm hoping there's a way to do something similar in VBA, without having to reinvent the wheel.</p>
  
  <p>Any ideas are appreciated.</p>