[AccessD] Two Column Table Convert Rows To Columns

[Paul Hartland]

Thank you so very much

On 28 April 2016 at 16:23, jack drawbridge <jackandpat.d at gmail.com> wrote:

> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com