Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Unanswered: Need help with combining Text from many rows into one

    I have a query that returns comments as strings of text in rows within the same column. I need a way of grouping the result into one field only. MySQL looks like this at present: Can you help?

    SELECT T_PSWS_ENTRY.Q1_Comment
    FROM T_PSWS_ENTRY
    GROUP BY T_PSWS_ENTRY.[Event Date], T_PSWS_ENTRY.Modules, T_PSWS_ENTRY.Q1_Comment
    HAVING (((T_PSWS_ENTRY.[Event Date])=[Enter the Event Date:]) AND ((T_PSWS_ENTRY.Modules)=[Enter Modules]));

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You can always concatenate several columns into one:
    Code:
    SELECT Nz([Column1], '') & Nz([Column2], '') & ... AS Comment
    FROM ..
    .
    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28
    Yes but my problem is I only want the rows from 1 column concatenated to each other in the query result.
    For example if in the 1st row of the column there was a comment such as: helpers are great and in the next row down in the same column there was a comment saying and dbforum helpers are the best then I am looking for a way of the query returning one row stating: helpers are great, and dbforum helpers are the best
    Last edited by oldteddybear; 12-23-13 at 04:27.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the only way I can see of doing that is to write a function that does it for you, EG:-

    Code:
    public Function GetComments(KeyColumn as whateverdatatype) as string
    'do whatever validation you need
    'open a recordset 
    'iterate through your recordset to append values
    GetComments = "" 'set up our default return status
    if rs.bof = true and rs.eof = true then ' we have no results
      
    else
      while rs.eof = false
        if len(rs.MyCommentColumn) > 0 then
          GetComments = GetComments & rs.MyCommentColumn & "; " 'add the current comments column
    'or if you want separate lines
          GetComments = GetComments & rs.MyCommentColumn & vbcrlf
        rs.movenext
      wend
    endif
    you could make the function more generic by say adding a parameter that specifies what the separator should be, so the same function coudl be used to append comments in line or on separate lines.

    call the function eg GetComments(MyID Column)
    you cna do that in a query or a control in a form or report
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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