Comma separated values in one column in Access

Hi

I have a database with customer ID and their answer as shown bellow. What I would like to do is create a query that will group by ID and show Answer separated by comma.


Table

ID Answer
1 a
1 b
2 a
2 b
5 a
5 b

Query
1 a,b
2 a,b
5 a,b

Can you please help me out? I don't know much about SQL.

Thank you very much in advance!

This should be the answer but it does not work:

SELECT p_id,GROUP_CONCAT(participant_answer ORDER BY p_id SEPARATOR ',') 
	     AS participant_answers 
FROM table_name 
GROUP BY p_id ORDER BY p_id;

Code (markup):

 

I really think this is good question. Answers is Yes, you can but not always. When we pivot the table we use aggregated functions.

 

Anyone knows how to solve that?

 

I found this module that should do the trick and it works in the sample database. But it gives me user-defined type not defined... error in my. Sample db is mdb while I have 2007 access db

Module:

Option Compare Database
Option Explicit

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim SQL As String
  Dim vFld As Variant
  
  Set cnn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  vFld = Null
  
  SQL = "SELECT [" & Fieldx & "] as Fld" & _
        " FROM [" & Source & "]" & _
        " WHERE [" & Identity & "]=" & Value
  
  ' open recordset.
  rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
  
  ' concatenate the field.
  Do While Not rs.EOF
    If Not IsNull(rs!Fld) Then
      vFld = vFld & ", " & rs!Fld
    End If
    rs.MoveNext
  Loop
  ' remove leading comma and space.
  vFld = Mid(vFld, 3)
  
  Set cnn = Nothing
  Set rs = Nothing
  
  ' return concatenated string.
  Conc = vFld
End Function

Code (markup):

Can someone please help? Thanks!