Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    12

    Unanswered: Joining one field's returned rows into one

    Hey,

    I need some help with access SQL. Imagine this table:

    TABLE
    col1
    col2
    col3

    I need to select col3 FROM table; But I need it to be one row, with the values seperated by commas, instead of multiple rows. Then I need to take the first value of this list and assign it to a seperate variable and remove it from the list.

    Any help would be appreciated.

    Regards,
    - Mist

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok ive written a quick udf to do this


    Code:
    Function Combine(FieldName As String, TableName As String) As String
        
        Dim cmd As ADODB.Command
        Dim rst As ADODB.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT " & FieldName & " FROM " & TableName
        
        Set cmd = New ADODB.Command
        Set rst = New ADODB.Recordset
        
        cmd.ActiveConnection = CurrentProject.Connection
        cmd.CommandText = strSQL
        Set rst = cmd.Execute
        
        With rst
            .MoveFirst
            Do While Not .EOF
                Combine = Combine & "," & .Fields(0).Value
                .MoveNext
            Loop
        End With
        
        Combine = Mid$(Combine, 2, Len(Combine) - 1)
        Debug.Print OutPut
        
        Set rst = Nothing
        Set cmd = Nothing
        
    End Function
    and then run your SQL like this
    Code:
    SELECT Combine("cityid","tblCity") AS Expr1;
    for part 2 just use left and right functions

    HTH
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •