Create a concatenated list of child table values f

Question

I have an Access 2010 database with a relationship between parent and child tables. I would like to be able to query the database from an external application and show values from the child table as a concatenated list of values in a single column, similar to what MySQL can produce with its GROUP_CONCAT() function.

This has been asked here many times before, e.g., here:

Combine values from related rows into a single concatenated string value

but those solutions rely on a custom VBA function that is not available to external queries.

Is there a way to make such a concatenated list available to external queries without having to manually build the list in the other application?

Answer 1

Issue

Historically, the Access solution for a GROUP_CONCAT()-type query has been to use a VBA function like Allen Browne's ConcatRelated() (ref: here). However, custom VBA functions are only available to queries run from within Microsoft Access itself, so this is not a viable solution for queries against an Access database from some other application (e.g., a .NET application using OLEDB or ODBC).

Solution

With an Access 2010 (or newer) database we can emulate the behaviour of a MySQL GROUP_CONCAT() query by adding a Long Text ("Memo") field to the parent table and using data macros on the child table to maintain the concatenated list.

For example, for tables [Parents] ...

ParentID  ParentName   
--------  -------------
       1  Homer Simpson
       2  Ned Flanders 

... and [Children] ...

ChildID  ParentID  ChildName          DisplayOrder
-------  --------  -----------------  ------------
      1         1  Lisa                          2
      2         1  Bart                          1
      3         2  Rod, the elder                1
      4         1  Maggie                        3
      5         2  Todd, the younger             2

... we can add a new Memo/Long Text field named [ChildList] to the [Parents] table and then add the following data macros to the [Children] table:

[Named Macro: UpdateChildList]

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
    <DataMacro Name="UpdateChildList">
        <Parameters>
            <Parameter Name="prmParentID"/>
        </Parameters>
        <Statements>
            <Action Collapsed="true" Name="SetLocalVar">
                <Argument Name="Name">newList</Argument>
                <Argument Name="Value">Null</Argument>
            </Action>
            <ForEachRecord>
                <Data Alias="c">
                    <Query>
                        <References>
                            <Reference Source="Children" Alias="c"/>
                        </References>
                        <Results>
                            <Property Source="c" Name="ChildName"/>
                        </Results>
                        <Ordering>
                            <Order Source="c" Name="DisplayOrder"/>
                        </Ordering>
                    </Query>
                    <WhereCondition>[c].[ParentID]=[prmParentID] And [c].[ChildName] Is Not Null</WhereCondition>
                </Data>
                <Statements>
                    <ConditionalBlock>
                        <If>
                            <Condition>Not IsNull([newList])</Condition>
                            <Statements>
                                <Action Collapsed="true" Name="SetLocalVar">
                                    <Argument Name="Name">newList</Argument>
                                    <Argument Name="Value">[newList] &amp; ";" &amp; Chr(160)</Argument>
                                </Action>
                            </Statements>
                        </If>
                    </ConditionalBlock>
                    <Action Collapsed="true" Name="SetLocalVar">
                        <Argument Name="Name">newList</Argument>
                        <Argument Name="Value">[newList] &amp; [c].[ChildName]</Argument>
                    </Action>
                </Statements>
            </ForEachRecord>
            <LookUpRecord>
                <Data>
                    <Reference>Parents</Reference>
                    <WhereCondition>[Parents].[ParentID]=[prmParentID]</WhereCondition>
                </Data>
                <Statements>
                    <EditRecord>
                        <Data/>
                        <Statements>
                            <Action Collapsed="true" Name="SetField">
                                <Argument Name="Field">Parents.ChildList</Argument>
                                <Argument Name="Value">[newList]</Argument>
                            </Action>
                        </Statements>
                    </EditRecord>
                </Statements>
            </LookUpRecord>
        </Statements>
    </DataMacro>
</DataMacros>

[After Insert]

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
    <DataMacro Event="AfterInsert">
        <Statements>
            <Action Name="RunDataMacro">
                <Argument Name="MacroName">Children.UpdateChildList</Argument>
                <Parameters>
                    <Parameter Name="prmParentID" Value="[ParentID]"/>
                </Parameters>
            </Action>
        </Statements>
    </DataMacro>
</DataMacros>

[After Update]

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
    <DataMacro Event="AfterUpdate">
        <Statements>
            <ConditionalBlock>
                <If>
                    <Condition>Updated("ParentID") Or Updated("ChildName")</Condition>
                    <Statements>
                        <Action Name="RunDataMacro">
                            <Argument Name="MacroName">Children.UpdateChildList</Argument>
                            <Parameters>
                                <Parameter Name="prmParentID" Value="[ParentID]"/>
                            </Parameters>
                        </Action>
                        <ConditionalBlock>
                            <If>
                                <Condition>Updated("ParentID")</Condition>
                                <Statements>
                                    <Action Name="RunDataMacro">
                                        <Argument Name="MacroName">Children.UpdateChildList</Argument>
                                        <Parameters>
                                            <Parameter Name="prmParentID" Value="[Old].[ParentID]"/>
                                        </Parameters>
                                    </Action>
                                </Statements>
                            </If>
                        </ConditionalBlock>
                    </Statements>
                </If>
            </ConditionalBlock>
        </Statements>
    </DataMacro>
</DataMacros>

[After Delete]

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
    <DataMacro Event="AfterDelete">
        <Statements>
            <Action Name="RunDataMacro">
                <Argument Name="MacroName">Children.UpdateChildList</Argument>
                <Parameters>
                    <Parameter Name="prmParentID" Value="[Old].[ParentID]"/>
                </Parameters>
            </Action>
        </Statements>
    </DataMacro>
</DataMacros>

Results

As changes are made to the child table the list in the parent table will automatically be updated:

ParentID  ParentName     ChildList                        
--------  -------------  ---------------------------------
       1  Homer Simpson  Bart; Lisa; Maggie               
       2  Ned Flanders   Rod, the elder; Todd, the younger

Notes

1. The [ChildList] field is for display purposes only. Editing the values in that field will not change the values in the child table.

2. The list is separated with ";" & Chr(160) to differentiate it from any ";" & Chr(32) pairs that may be in the actual data. If the non-breaking space (Chr(160)) characters mess up wrapping of the list then we could use the Replace() function in our query to convert ";" & Chr(160) to ";" & Chr(32) or "," & Chr(32) or whatever would be most appropriate.

3. To populate the lists with existing child data we simply need to "update" one child record for each parent, like so

UPDATE Children SET ChildName=ChildName 
WHERE ChildID IN (SELECT MIN(ChildID) AS m FROM Children GROUP BY ParentID)