[AccessD] Two Column Table Convert Rows To Columns

[jack drawbridge]

Paul,
I created a table (PaulH) based on your data.

Item_No,    Branch_Code
12345,        ABC
12345,        DTR
12345,        GTH
23456,        AAA
23456,        AAB

This is the query:
SELECT DISTINCT Item_no
, Concatrelated("Branch_code","PaulH","Item_no='" & [Item_no]& "'" ) AS
ConcatOnBranch
FROM PaulH;


This is the Result:
Item_no    ConcatOnBranch
12345    ABC , DTR , GTH
23456    AAA , AAB

Here is the ConcatRelated function from Allen Browne
from http://allenbrowne.com/func-concat.html

'---------------------------------------------------------------------------------------
' Procedure : ConcatRelated
' Author    : AllenBrowne
' Date      : 12-11-2011
' Purpose   :
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Public Function ConcatRelated(strField As String, _
    strtable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = " , ") As Variant  '  was " , "
10    On Error GoTo Err_Handler
          'Purpose:   Generate a concatenated string of related records.
          'Return:    String variant, or Null if no matches.
          'Arguments: strField = name of field to get results from and
concatenate.
          '           strTable = name of a table or query.
          '           strWhere = WHERE clause to choose the right values.
          '           strOrderBy = ORDER BY clause, for sorting the values.
          '           strSeparator = characters to use between the
concatenated values.
          'Notes:     1. Use square brackets around field/table names with
spaces or odd characters.
          '           2. strField can be a Multi-valued field (A2007 and
later), but strOrderBy cannot.
          '           3. Nulls are omitted, zero-length strings (ZLSs) are
returned as ZLSs.
          '           4. Returning more than 255 characters to a recordset
triggers this Access bug:
          '               http://allenbrowne.com/bug-16.html
          Dim rs As DAO.Recordset         'Related records
          Dim rsMV As DAO.Recordset       'Multi-valued field recordset
          Dim strSQL As String            'SQL statement
          Dim strOut As String            'Output string to concatenate to.
          Dim lngLen As Long              'Length of string.
          Dim bIsMultiValue As Boolean    'Flag if strField is a
multi-valued field.

          'Initialize to Null
20        ConcatRelated = Null

          'Build SQL string, and get the records.
30        strSQL = "SELECT  " & strField & " FROM " & strtable   'distinct
jed@@@@@
40        If strWhere <> vbNullString Then
50            strSQL = strSQL & " WHERE " & strWhere
60        End If
70        If strOrderBy <> vbNullString Then
80            strSQL = strSQL & " ORDER BY " & strOrderBy
90        End If
100       Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
          'Determine if the requested field is multi-valued (Type is above
100.)
110       bIsMultiValue = (rs(0).Type > 100)

          'Loop through the matching records
120       Do While Not rs.EOF
130           If bIsMultiValue Then
                  'For multi-valued field, loop through the values
140               Set rsMV = rs(0).Value
150               Do While Not rsMV.EOF
160                   If Not IsNull(rsMV(0)) Then
170                       strOut = strOut & rsMV(0) & strSeparator

180                   End If
190                   rsMV.MoveNext
200               Loop
210               Set rsMV = Nothing
220           ElseIf Not IsNull(rs(0)) Then
230               strOut = strOut & rs(0) & strSeparator
240           End If
250           rs.MoveNext
260       Loop
270       rs.Close

          'Return the string without the trailing separator.
280       lngLen = Len(strOut) - Len(strSeparator)
290       If lngLen > 0 Then
300           ConcatRelated = Left(strOut, lngLen)
310       End If

Exit_Handler:
          'Clean up
320       Set rsMV = Nothing
330       Set rs = Nothing
340       Exit Function

Err_Handler:
350       MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "ConcatRelated()"
360       Resume Exit_Handler
End Function


Good luck.

On Thu, Apr 28, 2016 at 10:36 AM, Paul Hartland <
paul.hartland at googlemail.com> wrote:

> To all,
>
> Having a bit of a brain freeze here and can't seem to find the answer in
> google....I have a table with two columns Item_No, Branch_Code and I will
> have data similar to below:
>
> Item_No    Branch_Code
> 12345        ABC
> 12345        DTR
> 12345        GTH
> 23456        AAA
> 23456        AAB
>
> and what I need is
>
> Item_No      Branch_Code
> 12345          ABC, DTR, GTH
> 23456          AAA, AAB
>
> Could someone please point me in the right direction, this sounds as if it
> should be simple enough and I am 100% sure I have done this before....
>
> MANY MANY thanks in advance for any help on this.
>
>
>
> --
> Paul Hartland
> paul.hartland at googlemail.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>