Solved

Access Query concatenation

Posted on 2016-10-20
12
45 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 18
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 18
ID: 41853142
in my LoopAndCombine code, the Tablename parameter can also be a Queryname
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41853972
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 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

19 Experts available now in Live!

Get 1:1 Help Now