Solved

combine many return data from field in a new query INTO one field

Posted on 2016-10-28
5
36 Views
Last Modified: 2016-11-08
Access 2010

I have a query that returns a "many" result.

example:
SKU               EXPORT_ME          COUNTRY_CODE      
2WE34                   N                           AR
2WE34                   N                           BE
2WE34                   N                           ZE
2X464S                  Y                                                      
3WWW1                N                           RD
3WWW1                N                           SW
3WWW1                N                           CN
3WWW1                N                           HU
3WWC4                 Y                          

If the "SKU" is repeated then combine the data in the "COUNTRY_CODE"field together



The result would be
SKU               EXPORT_ME          COUNTRY_CODE      
2WE34                   N                           AR;BE;ZE
2X464S                  Y                                                      
3WWW1                N                           RD; SW; CN;HU
3WWC4                 Y                            



Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 3
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41864533
place this code in a regular module

Function ConcatThem(xVar As String)
  Dim rs As DAO.Recordset, sql As String, strCombine As String
  sql = "Select Country_Code From TableX Where SKU ='" & xVar & "' And COUNTRY_CODE Is not Null"
  Set rs = CurrentDb.OpenRecordset(sql)
  Do Until rs.EOF
      strCombine = strCombine & ";" & rs!Country_Code

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

  End Function

then create a query like this

select SKU, EXPORT_ME, concatthem([SKU]) as  CountryCode
from tableX
group by SKU, EXPORT_ME, concatthem([SKU])
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 41864581
Rey's post above should cover the immediate need.  If you need a generalized approach to grouped concatenation, please see my article on the subject here.

From that article, add this UDF to a regular VBA module:

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, the
    '   columns used for the sort.  As you would in an ORDER BY clause, use Asc or Desc to
    '   indicate whether the column is sorted ascending or descending.  If Asc/Desc is
    '   omitted, Asc is assumed by the query engine.  Note that if you use any columns
    '   in the Sort argument that are not in the ConcatColumns argument, you must use
    '   False for the Distinct argument
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null
    
    DConcat = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        IIf(Sort <> "", "ORDER BY " & Sort, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = DBEngine(0)(0).OpenRecordset(SQL, dbOpenForwardOnly)
    With rs
        Do Until .EOF
            
            ' Initialize variable for this row
            
            ThisItem = ""
            
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function

Open in new window



Then use it in a query like this:

SELECT SKU, EXPORT_ME, DConcat("[COUNTRY_CODE]", "[tbl_name]", "[SKU] = '" & SKU & "' And [EXPORT_ME] = '" & EXPORT_ME & "'", "; ") AS Country_Codes
FROM tbl_name
GROUP BY SKU, EXPORT_ME
ORDER BY SKU, EXPORT_ME

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 41869194
checking these out...Thanks !
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41869609
rey, I have checked and double checked your routine.
It is just taking over an hour to return data.

Patrick, working very quickly.
1
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41879243
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

707 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

18 Experts available now in Live!

Get 1:1 Help Now