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