SELECT DISTINCT AND GET ALL VALUES FOR EACH COLUMN SQL ACCESS

January 9, 2023

I have a table that have grouped all samples returned from the lab analysis of my work.
The problem is that some labs send different elements via different files, and now I’m facing problems.

I need to make a query that grab all values that are stored in different columns and assign it in just one row for each sample. Example:

          Batch Source_File Value Value 2
SAMPLE A   1        A        150     null
SAMPLE A   1        B        null    100
SAMPLE B   2        C        null    300
SAMPLE B   2        D        100     null

OUTPUT

         Batch Source_File Value Value 2
SAMPLE A   1       A,B      150   100
SAMPLE B   2       C,D      100   300

>Solution :

You can use plain SQL:

SELECT 
    Samples.Sample, 
    Samples.Batch, 
    Min([Source_File]) & "," & Max([Source_File]) AS Source_Files,
    Sum(Samples.Value) AS Sum1, 
    Sum(Samples.Value2) AS Sum2
FROM 
    Samples
GROUP BY 
    Samples.Sample, 
    Samples.Batch;

Output: