Concatenation of multiple entries in one row

Question

I am trying to concatenate a list of attributes for same products which are in different rows.

For example:
Column A (fruit_name) has fruit names and Column B (fruit_colors) has colors.
I want fruit color on the same row as the fruit.

|**fruit_name**  | **fruit_colors** |
|----------------|------------------|
|Apple           |Red               |
|Apple           |Yellow            |
|Apple           |Green             |
|Apple           |White             |
|Banana          |Red               |
|Banana          |Yellow            |
|Banana          |Green             |
|Banana          |White             |
|Plum            |White             |
|Plum            |Bluish            |
|Plum            |Purple            |

The result should be:

|**name**        | **colors**                |
|----------------|---------------------------|
|Apple           | Red, Yellow, Green, White |
|Banana          | Red, Yellow, Green, White |
|Plum            | White, Bluish, Purple     |

This is what I have:

Set fruit_name = rstsource.Fields("fruits")
Set source_fruit = rstsource.Fields("fruits_list_type")

rstsource.MoveFirst
count = rstsource.RecordCount
counter = 0

fruit_name = source_fruit
result = source_table
         
Do 
    Do
        counter = counter + 1
        result = result & ", " & source_table
        rstsource.MoveNext
                      
    Loop Until counter = count Or fruit_name <> source_fruit
         
    rstdest.AddNew
    rstdest.Fields("names") = fruit_name
    rstdest.Fields("colors") = result
    rstdest.Update
                
    fruit_name = source_fruit
    result = " "

Loop Until rstsource.EOF

This is the result - Some has comma on the front.

Banana - White, White
Apple - ,Yelow, Red
Banana- ,Red
Banana - White, White
Apple , Green
Plum - ,Green
Plum - ,Red
Banana - ,Red

At the end there is a

Run time error 3021.

Answer 1

How to create this query to combine values?

I have a table with the following structure and values: 

EventID PersonName 
----------- ------------ 
1 John 
1 Peter 
1 Sylvia 
2 John 
2 Sylvia 
3 Peter 
3 June 

---

I'd like to run a query and get results in the following format: 

EventID PersonNames 
-------- --------------- 
1 John, Peter, Sylvia 
2 John, Sylvia 
3 Peter, June 

Is there a query that will accomplish this?

Concatenate fields in same table
Author(s) Dev Ashish   
(Q)    I need to concatenate a field in the format "Value1; Value2; Value3" etc. for each unique value of another field in the same table.  How can I do this?
(A)    Using the fConcatFld function,  in the Northwind database, the following query should return a concatenated list of all CustomerIDs if you group by ContactTitle. 

SELECT ContactTitle, fConcatFld("Customers","ContactTitle","CustomerID","string",[ContactTitle]) AS CustomersFROM CustomersGROUP BY ContactTitle;
'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatFld(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'                "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

    On Error GoTo Err_fConcatFld

    lovConcat = Null
    Set lodb = CurrentDb

    loSQL = "SELECT [" & stFldToConcat & "] FROM ["
    loSQL = loSQL & stTable & "] WHERE "

    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
        Case Else
            GoTo Err_fConcatFld
    End Select

    Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

    'Are we sure that duplicates exist in stFldToConcat
    With lors
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not .EOF
                lovConcat = lovConcat & lors(stFldToConcat) & "; "
                .MoveNext
            Loop
        Else
            GoTo Exit_fConcatFld
        End If
    End With

    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatFld = Left(lovConcat, Len(lovConcat) - 2)


Exit_fConcatFld:
    Set lors = Nothing: Set lodb = Nothing
    Exit Function

Err_fConcatFld:
    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_fConcatFld
End Function
'************ Code End **********

Copy and paste the fConcatFld( ) function into a code module. Change the following VBA line of code:

lovConcat = lovConcat & lors(stFldToConcat) & "; "

to:

lovConcat = lovConcat & lors(stFldToConcat) & ", "

... then save it and compile the code.

Next, create a new query and open the query in SQL View and paste the following SQL statement into the SQL View pane:

SELECT EventID, fConcatFld("MyTable","EventID","PersonName","Long", EventID) 
AS PersonNames 
FROM MyTable 
GROUP BY EventID; 

... and replace "MyTable" with the name of your table. If the "EventID" data type isn't Long, then you'll need to replace this in the SQL statement, too, with whatever data type your field is using.

Save and run the query. Voila! Comma separated list.

Answer 2

I would have a read and download of Allen Browne's Concat function http://allenbrowne.com/func-concat.html - It will do exactly what you want.

This will be for report or display purposes only - you shouldn't store the data like this.