Making Concatenated Strings from Linked Tables and Multi-valued Fields

Access Archon Column #230 – Making Concatenated Strings from Linked Tables and Multi-valued Fields

Access versions: 2007-2013

Level: Intermediate


Introduction

Back in 2001, I wrote an Access Archon article (#89, Flattening Linked Data) which showed how to create a string of concatenated data from values in a one-to-many linked table. That technique is still useful, but since Access 2007 there has been another source of data that needs to be concatenated – values in Multi-valued fields. This article shows how to create concatenated strings from both of these types of data, writing the concatenated data to a temp table for purposes of exporting to Excel or some other application that doesn’t support linked tables.


The Problem

You might think that you could just use the Multi-valued field itself for this purpose, since it looks like a concatenated string already, when displayed on a form:

http://img.office-watch.com/waw/AW%20230-A.png image from Making Concatenated Strings from Linked Tables and Multi-valued Fields at Office-Watch.com

Figure A. A form with a multi-valued field

However, it turns out that such fields can’t be used in a Make-table query – that triggers this error message:

http://img.office-watch.com/waw/AW%20230-B.png image from Making Concatenated Strings from Linked Tables and Multi-valued Fields at Office-Watch.com

Figure B. An error when using a Multi-valued field in a Make-table query

Nor can they be used in an Append query – in that case, you get this error message:

http://img.office-watch.com/waw/AW%20230-C.png image from Making Concatenated Strings from Linked Tables and Multi-valued Fields at Office-Watch.com

Figure C. An error when using a Multi-valued field in an Append query


The Solution

In order to create a concatenated string of the values stored in a Multi-valued field, for export to a table that will be used (for example) for exporting to an Excel workbook, you will need to write some code. But first, make a query with a field that displays the values from the Multi-valued field:

http://img.office-watch.com/waw/AW%20230-D.png image from Making Concatenated Strings from Linked Tables and Multi-valued Fields at Office-Watch.com

Figure D. A query that returns the values stored in a Multi-valued field

It is a good idea to alias the field name; don’t use the name of the main field, because this could cause confusion in code. Here is what this query looks like in Datasheet view:

http://img.office-watch.com/waw/AW%20230-E.png image from Making Concatenated Strings from Linked Tables and Multi-valued Fields at Office-Watch.com

Figure E. The same query in Datasheet view

Three target tables are needed to achieve the final goal of filling a table with data listing multiple children per parent and multiple subjects per child in one record. The first table (tblParentsAndChildren) is filled from the qryParentsAndChildren query, using this code, which is run from the Concatenate Children button on frmParents:

Public Sub FillTable1()

On Error GoTo ErrorHandler

Dim strParentName As String

Dim lngPrevParent As Long

Dim lngThisParent As Long

Dim strThisChild As String

Dim strChildren As String

 

strQuery = “qryParentsAndChildren”

strTable = “tblParentsAndChildren”

 

‘Clear old target table

strSQL = “DELETE * FROM ” & strTable

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

 

Set rstSource = CurrentDb.OpenRecordset(strQuery, dbOpenDynaset)

Set rstTarget = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)

lngPrevParent = 0

With rstSource

‘Special processing for first record

lngThisParent = ![ParentID]

strParentName = ![ParentName]

strThisChild = ![FirstName]

Debug.Print “Current parent: ” & strParentName

Debug.Print “Current child: ” & strThisChild

rstTarget.AddNew

rstTarget![ParentID] = ![ParentID]

rstTarget![Parent] = strParentName

 

‘Add first child to variable

strChildren = strThisChild & “, “

Debug.Print “Children: ” & strChildren

lngPrevParent = lngThisParent

.MoveNext

 

Do While Not .EOF

lngThisParent = ![ParentID]

strParentName = ![ParentName]

strThisChild = ![FirstName]

Debug.Print “Current parent: ” & strParentName

Debug.Print “Current child: ” & strThisChild

 

If lngThisParent <> lngPrevParent Then

‘New parent; save strChildren variable to current record

‘and add new record to target table

Debug.Print “On new parent record”

strChildren = Left(strChildren, Len(strChildren) – 2)

Debug.Print “Final Children list: ” & strChildren

rstTarget![Children] = strChildren

rstTarget.Update

strChildren = “”

rstTarget.AddNew

rstTarget![ParentID] = ![ParentID]

rstTarget![Parent] = strParentName

‘Add first child to variable

strChildren = strThisChild & “, “

ElseIf lngThisParent = lngPrevParent Then

‘Same parent; add next child to variable

Debug.Print “On new record for same parent”

strChildren = strChildren & strThisChild & “, “

Debug.Print “Children: ” & strChildren

End If

lngPrevParent = lngThisParent

.MoveNext

Loop

 

‘Special processing for last record

strChildren = Left(strChildren, Len(strChildren) – 2)

rstTarget![Children] = strChildren

rstTarget.Update

.Close

rstTarget.Close

End With

 

Finish:

DoCmd.OpenTable strTable

ErrorHandlerExit:

Exit Sub

ErrorHandler:

MsgBox “Error No: ” & Err.Number _

& ” in FillTable1 procedure; ” _

& “Description: ” & Err.Description

Resume ErrorHandlerExit

End Sub

[Break here]

The following code will write a concatenated string of values for each subject per child, to the Subjects field in a tblChildrenAndSubjects. This code is run from the Concatenate Subjects button on frmParents

Public Sub FillTable2()

On Error GoTo ErrorHandler

Dim strThisSubject As String

Dim strSubjects As String

 

strQuery = “qryChildrenAndSubjects”

strTable = “tblChildrenAndSubjects”

 

‘Clear old target table

strSQL = “DELETE * FROM ” & strTable

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

 

‘Add data to table

Set rstSource = CurrentDb.OpenRecordset(strQuery)

Set rstTarget = CurrentDb.OpenRecordset(strTable)

 

lngPrevID = 0

lngPrevParentID = 0

strPrevChild = “”

 

With rstSource

‘Special processing for first record

lngThisID = ![ChildID]

lngThisParentID = ![ParentID]

Debug.Print “Processing ID ” & lngThisID

strThisChild = ![ChildName]

strThisSubject = Nz(![Subject])

Debug.Print “Current Subject: ” & strThisSubject

 

If strThisSubject <> “” Then

‘Add first Subject to variable

strSubjects = strThisSubject & “, “

‘Debug.Print “Subjects: ” & strSubjects

End If

 

lngPrevID = lngThisID

lngPrevParentID = lngThisParentID

strPrevChild = strThisChild

.MoveNext

 

StartLoop:

Do While Not .EOF

lngThisID = ![ChildID]

Debug.Print “Processing ID ” & lngThisID

lngThisParentID = ![ParentID]

strThisChild = ![ChildName]

strThisSubject = Nz(![Subject])

Debug.Print “Current Subject: ” & strThisSubject

 

If lngThisID <> lngPrevID Then

‘New ID; save strSubjects variable to record

Debug.Print “On new ID record”

 

If strSubjects <> “” Then

strSubjects = Left(strSubjects, Len(strSubjects) – 2)

End If

 

Debug.Print “Final Subjects list for ID ” & lngPrevID _

& “; Subjects: ” & strSubjects

 

‘Create a record in target database and write data to it

rstTarget.AddNew

rstTarget![ChildID] = lngPrevID

rstTarget![ParentID] = lngPrevParentID

rstTarget![Child] = strPrevChild

rstTarget![Subjects] = strSubjects

rstTarget.Update

 

strSubjects = “”

 

‘Add first Subject from current record to variable

strSubjects = strThisSubject & “, “

ElseIf lngThisID = lngPrevID Then

‘Same ID; add next Subject to variable

Debug.Print “On new record for same ID”

strSubjects = strSubjects & strThisSubject & “, “

Debug.Print “Subjects: ” & strSubjects

End If

 

NextSubject:

lngPrevID = lngThisID

lngPrevParentID = lngThisParentID

strPrevChild = strThisChild

.MoveNext

Loop

 

‘Special processing for last record

strSubjects = Left(strSubjects, Len(strSubjects) – 2)

rstTarget.AddNew

rstTarget![ChildID] = lngPrevID

rstTarget![ParentID] = lngPrevParentID

rstTarget![Child] = strPrevChild

rstTarget![Subjects] = strSubjects

rstTarget.Update

End With

 

Finish:

DoCmd.OpenTable strTable

 

ErrorHandlerExit:

rstTarget.Close

rstSource.Close

Set rstSource = Nothing

Set rstTarget = Nothing

 

Exit Sub

ErrorHandler:

MsgBox “Error No: ” & Err.Number _

& ” in FillTable2 procedure; ” _

& “Description: ” & Err.Description

Resume ErrorHandlerExit

End Sub

In most cases, all you need is one field filled with concatenated data from either a linked table or a Multi-valued field, as in tblParentsAndChildren or tblChildrenAndSubjects. But if you want a table with one row per parent, listing their children and the children’s subjects, that can be done, using a query that links tblChildrenAndSubjects and tblParents to get the parent names.

The final procedure takes the concatenated data from qryParentsChildrenAndSubjects to fill a third table (tblAllInOne) with data on parent, children and subjects in a compact format, one row per parent; it is run from the Concatenate All button on frmParents:

Public Sub FillTable3()

On Error GoTo ErrorHandler

Dim strThisParent As String

Dim strPrevParent As String

Dim strChildrenAndSubjects As String

 

strQuery = “qryParentsChildrenAndSubjects”

strTable = “tblAllInOne”

 

‘Clear old target table

strSQL = “DELETE * FROM ” & strTable

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

 

‘Add data to table

Set rstSource = CurrentDb.OpenRecordset(strQuery)

Set rstTarget = CurrentDb.OpenRecordset(strTable)

 

lngPrevParentID = 0

strPrevChild = “”

strPrevParent = “”

 

With rstSource

‘Special processing for first record

lngThisParentID = ![ParentID]

Debug.Print “Processing Parent ID ” & lngThisParentID

strThisParent = ![ParentName]

strThisChild = ![ChildrenAndSubjects]

lngPrevParentID = lngThisParentID

strChildrenAndSubjects = strThisChild & “; “

Debug.Print “First Children and Subjects: ” & strChildrenAndSubjects

strPrevChild = strThisChild

strPrevParent = strThisParent

.MoveNext

 

StartLoop:

Do While Not .EOF

lngThisParentID = ![ParentID]

Debug.Print “Processing Parent ID ” & lngThisParentID

strThisParent = ![ParentName]

strThisChild = ![ChildrenAndSubjects]

 

If lngThisParentID <> lngPrevParentID Then

‘New Parent ID; save strChildrenAndSubjects variable to record

Debug.Print “On new Parent ID record”

 

If strChildrenAndSubjects <> “” Then

strChildrenAndSubjects = Left(strChildrenAndSubjects, _

Len(strChildrenAndSubjects) – 2)

End If

 

If Left(strChildrenAndSubjects, 1) = “; ” Then

strChildrenAndSubjects = Mid(strChildrenAndSubjects, 3)

End If

 

Debug.Print “Final Children and Subjects list for Parent ID ” _

& lngPrevParentID & “: ” & strChildrenAndSubjects

 

‘Create a record in target database and write data to it

rstTarget.AddNew

rstTarget![ParentID] = lngPrevParentID

rstTarget![ParentName] = strPrevParent

rstTarget![ChildrenAndSubjects] = strChildrenAndSubjects

rstTarget.Update

 

strChildrenAndSubjects = “”

 

‘Add first value from current record to variable

strChildrenAndSubjects = strThisChild & “; “

ElseIf lngThisParentID = lngPrevParentID Then

‘Same Parent ID; add next Subject to variable

Debug.Print “On new record for same Parent ID”

strChildrenAndSubjects = strChildrenAndSubjects _

& strThisChild & “; “

Debug.Print “Children and Subjects: ” & strChildrenAndSubjects

End If

 

lngPrevParentID = lngThisParentID

strPrevChild = strThisChild

strPrevParent = strThisParent

.MoveNext

Loop

 

‘Special processing for last record

strChildrenAndSubjects = Left(strChildrenAndSubjects, _

Len(strChildrenAndSubjects) – 2)

rstTarget.AddNew

rstTarget![ParentID] = lngPrevParentID

rstTarget![ParentName] = strPrevParent

rstTarget![ChildrenAndSubjects] = strChildrenAndSubjects

rstTarget.Update

End With

 

Finish:

DoCmd.OpenTable strTable

 

ErrorHandlerExit:

rstTarget.Close

rstSource.Close

Set rstSource = Nothing

Set rstTarget = Nothing

 

Exit Sub

 

ErrorHandler:

MsgBox “Error No: ” & Err.Number _

& ” in FillTable3 procedure; ” _

& “Description: ” & Err.Description

Resume ErrorHandlerExit

 

End Sub

When each procedure is run, at the end it opens the table filled with concatenated data. Here are the three tables:

http://img.office-watch.com/waw/AW%20230-G.png image from Making Concatenated Strings from Linked Tables and Multi-valued Fields at Office-Watch.com

Figure G. The three tables filled with concatenated data


An Alternative to Multi-valued Fields

If possible, I recommend replacing Multi-valued fields with linked tables, using a Continuous Forms-type subform to allow choice of multiple values, which are stored in a junction table that supports a many-to-many relationship. Data in separate tables is much easier to work with in code, and you can set up a many-to-many relationship in an .mdb database, which does not support Multi-valued fields. See my Access Archon #178 (Entering Data for a Many-to-Many Relationship) for a discussion of creating a many-to-many link and setting up forms to add data to the linking table. One of the forms from this Access Archon article’s sample database is shown below:

http://img.office-watch.com/waw/AW%20230-F.png image from Making Concatenated Strings from Linked Tables and Multi-valued Fields at Office-Watch.com

Figure F. A form based on a many-to-many relationship


References

The code in the sample database does not need any special references.


Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch230.zip, which is the last entry in the table of Access Archon columns for Access Watch.











Document Name

Document Type

Place in

Making Concatenated Strings (AA 230).accdb

Access 2007/2010 database

Wherever you want