Concatenate Column Values from Multiple Rows into a Single Column

[Ikrion]

Hi Everyone,

This has been a great thread and is getting me oh so close. Wondering if anyone had some insight they could share. I'm nearly there, but can't seem to get what I need from a linked table to change my numerical DB key into the text that's associated with it.

I've setup a module (VBA) to concatenate the multiple rows and it works great. BUT, it only seems to work on the original table.

A couple of fields:

tblApprovalGiven.VerID: A unique number (not key) that records a document version
tblApprovalGiven.AppID: A unique number (not key) that records the ID of the approver
tblApprovers.fName: The first name of the approver. This table has [AppID] on it, and is lined to tblApprovalGiven.

Basically, I'm trying to get that [fName] to come over, instead of the [AppID].

When I run the following:

ApproverIDs: ConcatRelated("[AppID]","tblApprovalGiven","[tblApprovalGiven.VerID] = " & [tblApprovalGiven.VerID],"[AppID]","; ")

I get:

VerID ApproverIDs
59 2; 6; 12; 13; 35
67 1; 21
etc.

That's great, except I need names, and not the numbers. Everything I've tried so far seems to just send it into a tailspin :banghead:.

Any ideas?

Thanks!


*************
The SQL of the query:

SELECT DISTINCT qrySOPReleased.VerID, ConcatRelated("[AppID]","tblApprovalGiven","[tblApprovalGiven.VerID] = " & [tblApprovalGiven.VerID],"[AppID]","; ") AS ApproverIDs
FROM tblApprovers INNER JOIN (((tblApprovalGiven INNER JOIN qrySOPReleased ON tblApprovalGiven.VerID = qrySOPReleased.VerID) INNER JOIN tblSOPVer ON (tblSOPVer.VerID = tblApprovalGiven.VerID) AND (qrySOPReleased.VerID = tblSOPVer.VerID)) INNER JOIN qrySOPApproversWithNames ON tblSOPVer.VerID = qrySOPApproversWithNames.VerID) ON tblApprovers.AppID = tblApprovalGiven.AppID;

*****************

The VBA function being used (helpfully supplied elsewhere):


Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
'Purpose: Generate a concatenated string of related records.
'Return: String variant, or Null if no matches.
'Arguments: strField = name of field to get results from and concatenate.
' strTable = name of a table or query.
' strWhere = WHERE clause to choose the right values.
' strOrderBy = ORDER BY clause, for sorting the values.
' strSeparator = characters to use between the concatenated values.
'Notes: 1. Use square brackets around field/table names with spaces or odd characters.
' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
' 4. Returning more than 255 characters to a recordset triggers this Access bug:
'
Dim rs As DAO.Recordset 'Related records
Dim rsMV As DAO.Recordset 'Multi-valued field recordset
Dim strSql As String 'SQL statement
Dim strOut As String 'Output string to concatenate to.
Dim lngLen As Long 'Length of string.
Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.

'Initialize to Null
ConcatRelated = Null

'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy
End If
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
'Determine if the requested field is multi-valued (Type is above 100.)
bIsMultiValue = (rs(0).Type > 100)

'Loop through the matching records
Do While Not rs.EOF
If bIsMultiValue Then
'For multi-valued field, loop through the values
Set rsMV = rs(0).Value
Do While Not rsMV.EOF
If Not IsNull(rsMV(0)) Then
strOut = strOut & rsMV(0) & strSeparator
End If
rsMV.MoveNext
Loop
Set rsMV = Nothing
ElseIf Not IsNull(rs(0)) Then
strOut = strOut & rs(0) & strSeparator
End If
rs.MoveNext
Loop
rs.Close

'Return the string without the trailing separator.
lngLen = Len(strOut) - Len(strSeparator)
If lngLen > 0 Then
ConcatRelated = Left(strOut, lngLen)
End If

Exit_Handler:
'Clean up
Set rsMV = Nothing
Set rs = Nothing
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
Resume Exit_Handler
End Function

 

[Beetle]

If you want to return the fName then you need to use that as the strField argument and tblApprovers as the strTable argument. Also, your where clause is a bit off as you would need to compare the AppId fields from the two tables. It should probably look more like;

ApproverNames: ConcatRelated("[fName]","tblApprovers","[AppID] = " & [tblApprovalGiven].[AppID],"[fName]","; ")

 

[Ikrion]

If you want to return the fName then you need to use that as the strField argument and tblApprovers as the strTable argument. Also, your where clause is a bit off as you would need to compare the AppId fields from the two tables. It should probably look more like;

ApproverNames: ConcatRelated("[fName]","tblApprovers","[AppID] = " & [tblApprovalGiven].[AppID],"[fName]","; ")

Hey Beetle -

Thanks for the reply. That does get me some of the way there, but it seems to break the concat. The return I'm getting has names, but is structured as:

VerID ApproverIDs
59 Joe
59 Bob
59 Jim
144 Brad
144 John

As opposed to:

59 Joe; Bob; Jim
144 Brad; John

I've got exactly this in my query:

ApproverNames: ConcatRelated("[fName]","tblApprovers","[AppID] = " & [tblApprovalGiven].[AppID],"[fName]","; ")

I'm attaching a screenshot of the query if that helps (to show relationships)

 

[Ikrion]

Ok, Bettle, I think I've got it figured out.

The VBA can only look for data on the same table. I realized I was trying to push linked data from another table, hence the errors. The suggestion you had works with the link, but breaks concatenation.

This is what worked (for me at least).

I created a query to bring in the linked data into the same location (query). Then, I replaced all of the tblApprovalGiven references to my new query, and ran it again. It takes longer to run, but I still got the right concatenated returns. Seeing as that worked, I then replaced [AppID] with [fName]. Runs terribly slow, BUT, that works. It concatenates the names.

I'm attaching a screenshot of what I ended up with. Maybe it will help someone with a similar problem I've had, using of course the function from my previous post.

Again, thanks for getting me thinking!

 

[boblarson]

Get rid of one of the links between the tables. You should not have a circle there.

 

[PROLO]

Ola amigos (Hello friend)

abaixo uma consulta que funciona. (Above this query run good).
Concatena por linha (concatenate line).
A consulta é simples de escrever (this query is very easy for white and undestand)


SELECT [00].CPF, [62].Habilitacao, Sum([62].CH) AS SomaDeCH, First([62].[Disciplina]) & IIf(Count([62].[Disciplina])>1,"," & Last([62].[Disciplina])) AS Materias

FROM Sheet1 AS 00 INNER JOIN [GPE62_2014-08-12] AS 62 ON ([00].MATR = [62].CodigoSEAP) AND ([00].VINC = [62].VinculoSEAP)

GROUP BY [00].CPF, [62].Habilitacao;



Thank you.

Luciano - Brazil

 

[PROLO]

Ola amigos (Hello friend)

abaixo uma consulta que funciona. (Above this query run good).
Concatena por linha (concatenate line).
A consulta é simples de escrever (this query is very easy for white and undestand)


SELECT [00].CPF, [62].Habilitacao, Sum([62].CH) AS SomaDeCH, First([62].[Disciplina]) & IIf(Count([62].[Disciplina])>1,"," & Last([62].[Disciplina])) AS Materias

FROM Sheet1 AS 00 INNER JOIN [GPE62_2014-08-12] AS 62 ON ([00].MATR = [62].CodigoSEAP) AND ([00].VINC = [62].VinculoSEAP)

GROUP BY [00].CPF, [62].Habilitacao;



Thank you.

Luciano - Brazil

Other example:


SELECT [99].Ano, First([99].[GRUPO]) & IIf(Count([99].[GRUPO])>1,"," & Last([99].[GRUPO])) AS Materias

FROM 99

GROUP BY [99].Ano;

 

[PROLO]

Attach, image for example.

code:

SELECT [00].CPF, Sum([62].CH) AS SomaDeCH, First([62].[Disciplina]) & IIf(Count([62].[Disciplina])>1,"," & Last([62].[Disciplina])) AS Materias

FROM Sheet1 AS 00 INNER JOIN [GPE62_2014-08-12] AS 62 ON ([00].MATR = [62].CodigoSEAP) AND ([00].VINC = [62].VinculoSEAP)

GROUP BY [00].CPF;

 

[sm123]

Hello,

I've downloaded John K's databases and I am using the Conc() function. I have also altered the line of code to " WHERE [" & Identity & "]='" & Value & "'" to account for my ID's being text. This works great for the majority of the data i'm working with. However, this doesn't work for any ID with an apostrophe (') located somewhere in the text. Is there a workaround for this?

Thanks!

 

[Frothingslosh]

 

[Frothingslosh]

In answer to your question, try using the REPLACE function on the string in question and replace all apostrophes with two apostrophes (NOT the quote (") character- two consecutive apostrophe characters). This tells the compiler that the apostrophe is part of the string.