I am currently trying to SELECT the DISTINCT FirstNames in a GROUP, using Microsoft Access 2010. The simplified relevant columns of my table looks like this: 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

MS-Access SQL DISTINCT GROUP BY

I am currently trying to SELECT the DISTINCT FirstNames in a GROUP, using Microsoft Access 2010.
The simplified relevant columns of my table looks like this:

+----+-------------+-----------+
| ID | GroupNumber | FirstName |
+----+-------------+-----------+
|  1 |           1 | Peter     |
|  2 |           1 | Bob       |
|  3 |           1 | Peter     |
|  4 |           2 | Rosemary  |
|  5 |           2 | Jamie     |
|  6 |           3 | Peter     |
+----+-------------+-----------+

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.

And I would like it to return this:

+-------------+------------+
| GroupNumber | FirstNames |
+-------------+------------+
|           1 | Peter      |
|           1 | Bob        |
|           2 | Rosemary   |
|           2 | Jamie      |
|           3 | Peter      |
+-------------+------------+

This means that I want all Distinct FirstNames for each Group.
A regular DISTINCT 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 SELECTing columns that are not aggregates or in the GROUP BY statement.

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. (Standardized languages are a nice thing, aren’t they?)

My current (invalid) query looks like this:

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;

This query is a one time thing and is used to analyze a 58000 row excel spreadsheet exported from another Database (not my fault), so optimizing for runtime is not necessary.

I would like to achieve this purely through SQL and without VBA if at all possible.

Answer

This should work:

SELECT DISTINCT GroupNumber, FirstNames
FROM Example AS b

Advertisement

Answer