Combining Memo field from multiple records into one field

**Jim Doherty** · May 10 '10, 11:18 PM

Originally posted by allanb

Hi Guys, I am a newbie and need some help.

I have a query that contains a list of questions together with the result per question for a group of learners.
Example: Learner ID; Name, Surname, Question; Result and Comment.

If I run the query on one particular learner, there might be 5 records, each with a comment. I need to combine these comments into one field.
Example: 1st comment followed by 2nd commentfollowed by 3rd comment followed by 4th comment followed by 5th comment.

I need to display the records as well as this one field in a form. Can anyone help?

Thank you.

I see you have not had a contribution on this for a week.

Some advice do not use NAME as a word for naming fields. It is a reserved word and can cause problems. I notice you mention a query, my assumption is that your data is normalised properly and that your query gathers its source data from separate tables.

To help you, below is a function that expects a numeric 'learnerID' data type of the datatype LONG INTEGER to be passed to it. (If yours is text then it will have to be reflected in the SQL string where the LearnerID is mention)

To get this working open a New Module and paste the function directly into it and save it

The function now becomes available to your query in that you can add it as any other column for instance in the query grid just type in a new column the fieldname as

All Comments: CommentsAll([LearnerID])

and it will return the concatenation of ALL comments but it will display it for EACH row too. This is not so painful to look at if you are simply creating a form that displays a single question record. You can assign place a textbox on the form and set its controlsource to the additional column 'All Comments'

Below is the function

Code:

Function CommentsAll(learnerId As Long) As String
'loop a column of comments depending on the NUMERIC value of learnerid passed
'to the function and list the comments per record.
'but concatenated so that is is a list format suitable of use in a textbox
'the learnerid is assumed nueric value and that it exists in each row to identify
'the row.  If you dont want the vbNewline then replace with Space(1)
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim str As String
Dim mysql As String
Set db = CurrentDb
mysql = "SELECT Comment FROM tblLearner WHERE (((tblLearner.LearnerID)=" & learnerId & "));"
Set rs = db.OpenRecordset(mysql, dbOpenDynaset)
If rs.RecordCount <> 0 Then
i = 1
    Do While Not rs.EOF
        str = str & UCase("This is the comment for question (" & i & ")") & vbNewLine
        str = str & Nz(rs!Comment, "(No comment given)") & vbNewLine
        rs.MoveNext
    i = i + 1
    Loop
End If
Debug.Print str
CommentsAll = str
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

**NeoPa** · May 11 '10, 01:27 PM

Combining Rows-Opposite of Union may help.