Producing a List from Multiple Records

[NeoPa]

A number of posters have asked to be shown how to produce a list of items from multiple records which are (potentially) grouped together.
Take the following data for instance (from a table called [tblForum]) :

Zone             Forum
Community        Introductions
Community        Community Cafe
Community        Software Development
Community        Jobs / Contract Work
Community        Experts Panel
Programming      C++ / C
Programming      Java
Programming      .NET
Programming      Visual Basic
Programming      Python
Web Development  PHP
Web Development  Ruby / Rails
Web Development  Perl
Web Development  ASP
Web Development  ColdFusion
Web Development  Javascript / Ajax
Web Development  Flash
Web Development  XML
Web Development  WAP / WML
Web Development  HTML / CSS
Database Help    MySQL
Database Help    Oracle
Database Help    SQL Server
Database Help    PostgreSQL
Database Help    Access
Database Help    DB2
Sys Admin & OS   Apache
Sys Admin & OS   IIS
Sys Admin & OS   Networking
Sys Admin & OS   Linux / Unix / BSD
Sys Admin & OS   Windows

If you wanted this to be grouped together into lists (as below) then read on.

Zone             Forums
Community        Introductions, Community Cafe, Software Development, Jobs / Contract Work, Experts Panel
Programming      C++ / C, Java, .NET, Visual Basic, Python
Web Development  PHP, Ruby / Rails, Perl, ASP, ColdFusion, Javascript / Ajax, Flash, XML, WAP / WML, HTML / CSS
Database Help    MySQL, Oracle, SQL Server, PostgreSQL, Access, DB2
Sys Admin & OS   Apache, IIS, Networking, Linux / Unix / BSD, Windows

Firstly, create a function in a standard module similar to the example code attached :

'Concat Returns lists of items which are within a grouped field
Public Function Concat(strGroup As String, _
                       strItem As String) As String
    Static strLastGroup As String
    Static strItems As String

    If strGroup = strLastGroup Then
        strItems = strItems & ", " & strItem
    Else
        strLastGroup = strGroup
        strItems = strItem
    End If
    Concat = strItems
End Function

The SQL for calling this function would be of the form :

SELECT   [Zone],
         Max(Concat([Zone], [Forum])) AS [Forums]
FROM     [tblForum]
GROUP BY [Zone]

Another form of this (to drop duplicates) would be :

'Concat Returns lists of items which are within a grouped field
Public Function Concat(strGroup As String, _
                       strItem As String) As String
    Static strLastGroup As String
    Static strItems As String

    If strGroup = strLastGroup Then
        If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then _
            strItems = strItems & ", " & strItem
    Else
        strLastGroup = strGroup
        strItems = strItem
    End If
    Concat = strItems
End Function

[Mariostg]

This is nice.

I combined the two functions by adding a third parameter "uniq"

'Concat Returns lists of items which are within a grouped field
'Set uniq=-1 (True) to return only unique values.
Public Function ConcatLine(strGroup As String, _
                       strItem As String, uniq As Boolean) As String
    Static strLastGroup As String
    Static strItems As String
    Static i As Integer
  
    If strGroup = strLastGroup Then
        If uniq Then
            If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then
                strItems = strItems & ", " & strItem
            End If
        Else
            strItems = strItems & ", " & strItem
        End If
    Else
        strLastGroup = strGroup
        strItems = strItem
    End If
    ConcatLine = strItems
    i = i + 1
    Debug.Print i & " "; Len(ConcatLine) & " " & ConcatLine
End Function