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]) :

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 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 :

Expand|Select|Wrap|Line Numbers

 '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 :

Expand|Select|Wrap|Line Numbers

 SELECT [Zone],

  Max(Concat([Zone], [Forum])) AS [Forums]

FROM [tblForum]

GROUP BY [Zone]

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

Expand|Select|Wrap|Line Numbers

 '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

Producing a List from Multiple Records

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about