0

I've tried many different methods to join the following from;

StockCode       Finished_Goods_Codes
100137           2105109
100137           2105110
100137           2105111

To; 

StockCode        Finished_Goods_Codes
100137           2105109, 2105110, 2105111

My Current Code is as follows; 

Public Function ListQuery()
 Dim curr As Database
    Dim rs As Recordset
    Dim SQLCmd As String
    Dim productList As String

    Set curr = CurrentDb()

    SQLCmd = "SELECT Finished_Goods_Codes FROM TEMP_codes WHERE [StockCode] = """ & StockCode & """"

    Set rs = curr.OpenRecordset(SQLCmd)

    If Not rs.EOF Then
        rs.MoveFirst
    End If

    Do While Not rs.EOF
        productList = productList & rs(0) & ", "
        rs.MoveNext
    Loop

    ListQuery = productList
End Function

My Query currently runs the following; 

SELECT TEMP_codes.StockCode, ListQuery([Products]) AS [List of Products]
FROM TEMP_codes
GROUP BY TEMP_codes.StockCode;

Could you please help as i'm really stuck on this. Many Thanks in advance.

Chrislaar123
  • 323
  • 2
  • 6
  • 17

1 Answers1

1

Based on the answer given for the question Microsoft Access condense multiple lines in a table, here are the steps:

1 Create the following function

Public Function GetList(SQL As String _
                            , Optional ColumnDelimeter As String = ", " _
                            , Optional RowDelimeter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
'   1. SQL is a valid Select statement
'   2. ColumnDelimiter is the character(s) that separate each column
'   3. RowDelimiter is the character(s) that separate each row
'RETURN VAL: Concatenated list
'DESIGN NOTES:
'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key)

Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String

On Error GoTo ProcErr

Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)

sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)

If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
    sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If

GetList = sResult
oRS.Close
oConn.Close

CleanUp:
    Set oRS = Nothing
    Set oConn = Nothing

Exit Function
ProcErr:
    ' insert error handler
    Resume CleanUp

End Function

2 Add a Reference for the function in the Module (Tools -> References). Add the Reference Micorosft ActiveX Data Objects 6.1 Library (or the most recent one available).

3 Save the Module with a name different from the function name, say Concatenation

4 Run the following query

SELECT T.StockCode, GetList("Select Finished_Goods_Codes From TEMP_codes As T1 Where T1.StockCode = " & [T].[StockCode],"",", ") AS Finished_Goods_Codes
FROM TEMP_codes AS T
GROUP BY T.StockCode;
Community
  • 1
  • 1
Joseph B
  • 5,519
  • 1
  • 15
  • 19
  • I've just run this and it groups the [StockCode] but leaves the [Finished_Goods_Codes] field empty. Does it matter what Datatype the fields are? Both are currently set as Short Text. – Chrislaar123 May 29 '14 at 13:47
  • I tested with both columns as Number. I'll try it with Short Text. – Joseph B May 29 '14 at 13:53
  • 1
    Yeah, it does not work if the columns are Short Text. Please change them to Number. – Joseph B May 29 '14 at 13:54
  • Ahh, thats where my issue is then. I've just tested and it works. Do you if this same function can be used with short text? – Chrislaar123 May 29 '14 at 13:58
  • That's good. I would use the Number data type anyway, since the values are all numeric. The function does not seem to work for Short Text. – Joseph B May 29 '14 at 14:01