Concatenate mutilpe rows of data into a single field

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
I found the following code on ee
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24977680.html?sfQueryTermInfo=1+10+30+concat+data

However, when I modify it for my needs it is returning the same value for every row.

I need to look at an WS_NO_UO and group together every instances of the MN into the same field.

Why is not moving to the next record of the APNo and returning its list of MN?

here is my code - KEEP IN MIND THAT I USE GLOBAL DECLARATIONS FOR MOST OF MY VARIABLES.

I also need to be able to handle empty values that will not return the following example:

, , , , , , ,  123435, 5435665, 24342,

Also hide the last comma.

Thanks
K
Public Function Concat(WSNO As String) As String

Dim strTopic As String
StrSQL = "SELECT APNo, WS_NO_UO, MN" & _
        " FROM Qry_PrgUODetail" & _
        " GROUP BY APNo, WS_NO_UO, MN" & _
        " ORDER BY WS_NO_UO"
     '   " WHERE WS_NO_UO = " & Chr(34) & gWSNO & Chr(34) & "" & _

Set rs = CurrentDb.OpenRecordset(StrSQL)
'Debug.Print StrSQL
    rs.MoveFirst

    Do Until rs.EOF
        strTopic = strTopic & rs![MN] & ", "
        rs.MoveNext
    Loop

    If Len(strTopic & "") > 0 Then
        strTopic = Left(strTopic, Len(strTopic) - 2)
    Else
        strTopic = ""
    End If

    Concat = strTopic

End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
update your sql and use parameter passed to the function like:

StrSQL = "SELECT APNo, WS_NO_UO, MN" & _
        " FROM Qry_PrgUODetail" & _
        " WHERE WS_NO_UO = " & Chr(34) & gWSNO & Chr(34) & "" & _
        " GROUP BY APNo, WS_NO_UO, MN" & _
        " ORDER BY WS_NO_UO"
HainKurtSr. System Analyst

Commented:
a small fix, param name is WSNO:
StrSQL = "SELECT APNo, WS_NO_UO, MN" & _
        " FROM Qry_PrgUODetail" & _
        " WHERE WS_NO_UO = '" & WSNO & "'" & _
        " GROUP BY APNo, WS_NO_UO, MN" & _
        " ORDER BY WS_NO_UO"

Open in new window

Karen SchaeferBI ANALYST

Author

Commented:
problem is since I am using this within a query the parameter can not be set - I need to loop thru each WS_UO_NO and pass that value.

k

Commented:
A query is very structured and must be presented in a specific order:

SELECT
FROM
WHERE
GROUP BY
ORDER BY

not:

SELECT
FROM
GROUP BY
ORDER BY
WHERE
Karen SchaeferBI ANALYST

Author

Commented:
no I was commenting out the WHere clause -

problem is since I am using this within a query the parameter can not be set - I need to loop thru each WS_UO_NO and pass that value.
Top Expert 2010
Commented:
You may want to use my DConcat function instead, which is described in my article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

If I am reading your requirements right, your query could then become...


SELECT APNo, WS_NO_UO, DConcat("[MN]", "[Qry_PrgUODetail]", "[APNo] = " & [APNo] & " And [WS_NO_UO] = " & [WS_NO_UO]) AS Concat
FROM Qry_PrgUODetail
GROUP BY APNo, WS_NO_UO

If APNo is text and not numeric...

SELECT APNo, WS_NO_UO, DConcat("[MN]", "[Qry_PrgUODetail]", "[APNo] = '" & [APNo] & "' And [WS_NO_UO] = " & [WS_NO_UO]) AS Concat
FROM Qry_PrgUODetail
GROUP BY APNo, WS_NO_UO

If WS_NO_UO is not numeric...

SELECT APNo, WS_NO_UO, DConcat("[MN]", "[Qry_PrgUODetail]", "[APNo] = " & [APNo] & " And [WS_NO_UO] = '" & [WS_NO_UO] & "'") AS Concat
FROM Qry_PrgUODetail
GROUP BY APNo, WS_NO_UO

If neither APNo nor WS_NO_UO are numeric...

SELECT APNo, WS_NO_UO, DConcat("[MN]", "[Qry_PrgUODetail]", "[APNo] = '" & [APNo] & "' And [WS_NO_UO] = '" & [WS_NO_UO] & "'") AS Concat
FROM Qry_PrgUODetail
GROUP BY APNo, WS_NO_UO



>> KEEP IN MIND THAT I USE GLOBAL DECLARATIONS FOR MOST OF MY VARIABLES

With respect, that is a bad idea :)
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 = "Asc", _
    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, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' 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 & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
        
    ' 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 = CurrentDb.OpenRecordset(SQL)
    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

Karen SchaeferBI ANALYST

Author

Commented:
Thanks for the suggestion however, I need to modify your query statement.

I need to get a list of all MN (Number) per each WS_NO_UO (text).

I am currently using the following - need to remove the AP_NO

SELECT APNo, WS_NO_UO, DConcat("[MN]", "[Qry_PrgUODetail]", "[APNo] = '" & [APNo] & "' And [WS_NO_UO] = '" & [WS_NO_UO] & "'") AS Concat
FROM Qry_PrgUODetail
GROUP BY APNo, WS_NO_UO

When I removed it the Dcant does not return any values:  Does not seem to be passing the WS_NO_UO value.

SELECT Qry_PrgUODetail.WS_NO_UO, DConcat("[MN]","[Qry_PrgUODetail]","[WS_NO_UO] = '" & [WS_NO_UO] & "'") AS MNList
FROM Qry_PrgUODetail
GROUP BY Qry_PrgUODetail.WS_NO_UO;
HainKurtSr. System Analyst

Commented:
whats the issue with 33139376?

just change the query in your original post:

maybe you need this:
StrSQL = "SELECT DISTINCT MN" & _
        " FROM Qry_PrgUODetail" & _
        " WHERE WS_NO_UO = '" & WSNO & "'" & _
        " ORDER BY MN"

Open in new window

Karen SchaeferBI ANALYST

Author

Commented:
Almost there - I need to do LTrim to remove any MN beginning with , 33003, 333, 3003

Where do you suggested I place this?
K
Top Expert 2010

Commented:
Try:SELECT WS_NO_UO, DConcat("[MN]", "[Qry_PrgUODetail]", "[WS_NO_UO] = '" & [WS_NO_UO] & "'") AS ConcatFROM Qry_PrgUODetailGROUP BY WS_NO_UONow, what criteria do you need applied to MN?
Karen SchaeferBI ANALYST

Author

Commented:
thanks, however, all I need to accomplish is trimming out the beginning comma

see 33139822
HainKurtSr. System Analyst

Commented:
handle null values

add a condition to your query

MN is not null

or

isnull(MN,'') != ''

and you already have a code to trim extra ", " from the end...
Karen SchaeferBI ANALYST

Author

Commented:
thanks for the assist this did the trick
Top Expert 2010

Commented:
Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
and click 'Yes' for the 'Was this helpful?' voting.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial