title: MS-Access SQL DISTINCT GROUP BY
body: <p>I am currently trying to <code>SELECT</code> the <code>DISTINCT FirstNames</code> in a <code>GROUP</code>, using Microsoft Access 2010.<br>
  The simplified relevant columns of my table looks like this:  </p>
  
  <pre><code>+----+-------------+-----------+
  | ID | GroupNumber | FirstName |
  +----+-------------+-----------+
  |  1 |           1 | Peter     |
  |  2 |           1 | Bob       |
  |  3 |           1 | Peter     |
  |  4 |           2 | Rosemary  |
  |  5 |           2 | Jamie     |
  |  6 |           3 | Peter     |
  +----+-------------+-----------+
  </code></pre>
  
  <p>My actual table contains two columns to which I want to apply this process (separately), but I should be able to simply repeat the process for the other column. The column group number is a simplification, my table actually groups all rows in a ten day interval together, but I've already solved that problem.</p>
  
  <p>And I would like it to return this:</p>
  
  <pre><code>+-------------+------------+
  | GroupNumber | FirstNames |
  +-------------+------------+
  |           1 | Peter      |
  |           1 | Bob        |
  |           2 | Rosemary   |
  |           2 | Jamie      |
  |           3 | Peter      |
  +-------------+------------+
  </code></pre>
  
  <p>This means that I want all Distinct FirstNames for each Group.<br>
  A regular <code>DISTINCT</code> would ignore group boundaries and only mention Peter once. All aggregate functions reduce my output to only one value or don't work on strings at all. Access also doesn't support <code>SELECT</code>ing columns that are not aggregates or in the <code>GROUP BY</code> statement.  </p>
  
  <p>All other answers I've found either want an aggregate, are not applicable to MS Access or are solved by working around the data in ways not applicable to my case. <em>(Standardized languages are a nice thing, aren't they?)</em></p>
  
  <p>My current (invalid) query looks like this:</p>
  
  <pre><code>SELECT GroupNumber,
      DISTINCT FirstNames -- This is illegal, distinct applies to all
                          -- columns and doesn't respect groups.
  FROM Example AS b
  -- Complicated stuff to make the groups
  GROUP BY GroupNumber;
  </code></pre>
  
  <p>This query is a one time thing and is used to analyze a 58000 row <em>excel spreadsheet exported from another Database</em> (not my fault), so optimizing for runtime is not necessary. </p>
  
  <p>I would like to achieve this purely through SQL and without VBA if at all possible.</p>