summarize a table by concatenate a text field

[Lafayette]

I've got a table in the following structure:

Group# Name
1 Joe
1 Mike
2 Tom
2 Jack

The new table I have in mind looks like
Group# Names
1 Joe, Mike
2 Tom, Jack

It summarizes the original table by groups, combining the field "name".

Can it be done in query? Thanks for any advice.

 

[Danny J. Lesandrini]

I wrote about this in an article on DBJ ...

http://www.databasejournal.com/feat...ple-Rows-into-a-Single-Column-with-Access.htm


A hacked version of what you'd find in the download is below ...



Public Function ConcatColumns() As Boolean
On Error Resume Next

Dim db As DAO.Database
Dim rst As DAO.Recordset, sSQL As String
Dim strColumn1 As String
Dim strColumn2 As String

Set db = CurrentDb()
sSQL = "SELECT Column1, Column2 FROM tblOriginal " _
& "ORDER BY Column1, Column2 ASC"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
strColumn1 = rst!Column1
strColumn2 = rst!Column2

rst.MoveNext
Do Until rst.EOF
If strColumn1 = rst!Column1 Then
strColumn2 = strColumn2 & ", " & rst!Column2
Else
Debug.Print strColumn1 & "','" & strColumn2
strColumn1 = rst!Column1
strColumn2 = rst!Column2
End If
rst.MoveNext
Loop

' Output Last Record
Debug.Print strColumn1 & "','" & strColumn2
End If

Set rst = Nothing
Set db = Nothing

End Function

 

[Lafeyette]

Thank you very much for the codes.

Learned something about programming here