Access Query concatenation

Posted on 2016-10-20
Last Modified: 2016-10-21

I have a query that returns this:

ColumnA ColumnB
1                abc
1                pqr
1                xyz
2                efg
2                hij
3                asd

I want to make a query that returns this.

ColumnA ColumnB
1                abc, pqr, xyz
2                efg, hij
3                asd

Thanks for your help,
Question by:Genius123
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41853132
first place these codes in a Regular module, save module as modConcat

Function ConcatThem(xVar As Long)
Dim rs As DAO.Recordset, sql As String, strCombine As String
sql = "Select ColumnB From TableX Where ColumnA=" & xVar & " Order By ColumnB"
Set rs = CurrentDb.OpenRecordset(sql)
Do Until rs.EOF
    strCombine = strCombine & "," & rs!ColumnB

ConcatThem = Mid(strCombine, 2)

End Function

then create a query like this

Select ColumnA, ConcatThem([ColumnA])
from Tablex
Group By ColumnA
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41853135
then create a query like this

 Select ColumnA, ConcatThem([ColumnA])
 from Tablex
 Group By ColumnA, , ConcatThem([ColumnA])
LVL 20
ID: 41853138
here is a function to loop and combine:
'~~~~~~~~~~~~~~~~ LoopAndCombine
Function LoopAndCombine( _
   psTablename As String _
   , psIDFieldname As String _
   , psTextFieldname As String _
   , pnValueID As Long _
   , Optional psWhere As String = "" _
   , Optional psDeli As String = ", " _
   , Optional psNoValue As String = "" _
   , Optional psOrderBy As String = "" _
   ) As String
'loop through recordset and combine values to one string
   'a Microsoft DAO Library
   ' -- OR --
   ' Microsoft Office #.0 Access Database Engine Object Library
   'psTablename --> tablename to get list from
   'psIDFieldname --> fieldname to link on (ie: "BookID")
   'psTextFieldname --> fieldname to combine (ie: "PageNumber")
   'pnValueID --> actual value of ID for this iteration ( ie: [BookID])
   'psWhere, Optional  --> more criteria (ie: "Year(PubDate) = 2006")
   'psDeli, Optional  --> delimiter other than comma (ie: ";", Chr(13) & Chr(10))
   'psNoValue, Optional  --> value to use if no data (ie: "No Pages")
   'psOrderBy, Optional  --> fieldlist to Order By
   'Set up error handler
   On Error GoTo Proc_Err
   'dimension variables
   Dim rs As DAO.Recordset _
      , vAllValues As Variant _
      , sSQL As String
   vAllValues = Null
   sSQL = "SELECT [" & psTextFieldname & "] " _
       & " FROM [" & psTablename & "]" _
       & " WHERE [" & psIDFieldname _
       & "] = " & pnValueID _
       & IIf(Len(psWhere) > 0, " AND " & psWhere, "") _
       & IIf(Len(psOrderBy) > 0, " ORDER BY " & psOrderBy, "") _
       & ";"
   'open the recordset
   Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
   'loop through the recordset until the end
   With rs
      Do While Not rs.EOF
         If Not IsNull(.Fields(psTextFieldname)) Then
            '~~~~~~~~~~~~~~~~~~~~~~~~~ CHOOSE ONE
            '---- if field value is numeric
            vAllValues = (vAllValues + psDeli) _
             & Trim(.Fields(psTextFieldname))
            '---- uncomment if you want quotes around data
            'vAllValues = (vAllValues + psDeli) _
             & " '" & Trim(.Fields(psTextFieldname)) & "'"
          End If
   End With 'rs
   If Len(vAllValues) = 0 Then
      vAllValues = psNoValue
   End If
   'close the recordset
   'release the recordset variable
   Set rs = Nothing
   LoopAndCombine = Trim(Nz(vAllValues, ""))
   Exit Function
'if there is an error, the following code will execute
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
      & "   LoopAndCombine"
   Resume Proc_Exit
End Function

Open in new window

and here is code to test it:
'~~~~~~~~~~~~~~~~ testLoopAndCombine
Sub testLoopAndCombine()
'test LoopAndCombine
   Dim sTablename As String _
      , sIDFieldname As String _
      , sTextFieldname As String _
      , nValueID As Long _
      , sFieldSortBy As String
   sTablename = "MyTablename"
   sIDFieldname = "MyNumericForeignKeyFieldname" 'if FK is not a n umber, you will need to add delimiters to LoopAndCombine where it is referenced
   sTextFieldname = "Description of Fieldname"
   nValueID = 138 'some number you know is in the table
   sFieldSortBy = "FieldnameToSortBy"
   MsgBox LoopAndCombine(sTablename, sIDFieldname, sTextFieldname, nValueID, , , , sFieldSortBy)

End Sub

Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41853139
typo, too many commas on the query

Select ColumnA, ConcatThem([ColumnA])
  from Tablex
  Group By ColumnA,  ConcatThem([ColumnA])
LVL 20
ID: 41853142
in my LoopAndCombine code, the Tablename parameter can also be a Queryname
LVL 45

Expert Comment

ID: 41853972

Author Comment

ID: 41854262
Rey Obrero, I get a datatype mismatch error.  Shouldn't there be something that says group column A but concatenate column B?

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41854277
columnA must be Text data type, use this

Function ConcatThem(xVar As String)
 Dim rs As DAO.Recordset, sql As String, strCombine As String
 sql = "Select ColumnB From TableX Where ColumnA='" & xVar & "' Order By ColumnB"
 Set rs = CurrentDb.OpenRecordset(sql)
 Do Until rs.EOF
     strCombine = strCombine & "," & rs!ColumnB

 ConcatThem = Mid(strCombine, 2)

 End Function

Author Comment

ID: 41854338

I'm sorry, I cannot get this to work.  Would you mind taking a look at my attached Access file?  The table name is tblCodes.  The end result of the query should look like this:

BOM_PIECE_ID            MIL_CODE  
318238                         T3-SO,T3-MB,T3-JAMB-B,T3-057-CBR,T3-057-CBL


LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 41854354
here is the revised db, run Query1
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41854360
if you don't want the MIL_CODE sorted, change this line in the module

'  sql = "Select MIL_CODE From tblCodes Where BOM_PIECE_ID=" & xVar & " Order By MIL_CODE"
  sql = "Select MIL_CODE From tblCodes Where BOM_PIECE_ID=" & xVar

Author Closing Comment

ID: 41854362
Brilliant, thank you!

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question