Solved

Access Query concatenation

Posted on 2016-10-20
12
48 Views
Last Modified: 2016-10-21
Hello,

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,
Joel
0
Comment
Question by:Genius123
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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

rs.MoveNext
Loop
rs.Close
ConcatThem = Mid(strCombine, 2)

End Function


then create a query like this

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

Expert Comment

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

 Select ColumnA, ConcatThem([ColumnA])
 from Tablex
 Group By ColumnA, , ConcatThem([ColumnA])
0
 
LVL 19
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
's4p
'loop through recordset and combine values to one string
   
   'NEEDS REFERENCE
   'a Microsoft DAO Library
   ' -- OR --
   ' Microsoft Office #.0 Access Database Engine Object Library
   
   'PARAMETERS
   '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
         .MoveNext
      Loop
   End With 'rs
      
   If Len(vAllValues) = 0 Then
      vAllValues = psNoValue
   End If
 
   
Proc_Exit:
   'close the recordset
   rs.Close
   'release the recordset variable
   Set rs = Nothing
    
   LoopAndCombine = Trim(Nz(vAllValues, ""))
   Exit Function
   
'if there is an error, the following code will execute
Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
      & "   LoopAndCombine"
 
   Resume Proc_Exit
   Resume
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

0
 
LVL 119

Expert Comment

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

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

Expert Comment

by:aikimark
ID: 41853972
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

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

-Joel
0
 
LVL 119

Expert Comment

by:Rey Obrero
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

 rs.MoveNext
 Loop
 rs.Close
 ConcatThem = Mid(strCombine, 2)

 End Function
0
 

Author Comment

by:Genius123
ID: 41854338
Rey,

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

Thanks!

concat.mdb
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 41854354
here is the revised db, run Query1
concat.mdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
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"
with
  sql = "Select MIL_CODE From tblCodes Where BOM_PIECE_ID=" & xVar
0
 

Author Closing Comment

by:Genius123
ID: 41854362
Brilliant, thank you!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now