Merge cells from the same field - group by three other variables

[keusch]

I have a table which has the following fields: lastname, firstname, visitdate, and remark(text field).
lastname firstname visitdate remark
Johnson Len 5/17/61 Patient is feeling sluggish.
Johnson Len 5/17/61 Has difficulty breathing. Temperature
Johnson Len 5/17/61 102.5.
Johnson Len 6/21/61 Patient has had difficulty breathing for 4 weeks

I want to concantenate or merge the remark field for a given patient ( lastname and firstname) and visitdate into a newremark memo field.
To create:
lastname firstname visitdate newremark (memofield)
Johnson Len 5/17/61 Patient is feeling sluggish. Has difficulty breathing. Temperature 102.5.
Johnson Len 6/21/61 Patient has had difficulty breathing for 4 weeks

How can I do this in a simple query. I can write a macro in Excel but because many of the newremark cells exceed 255 char, some of the data is lost. It seems I should be able to do it in Access. Is there a DSum for text strings????

Thanks for your help

 

[rac2]

Hello,

This is something that cannot be done in a SQL query because the number of rows to be merged is variable. It must be done in a procedure, such as an Excel macro. You can write procedures in Access using VBA (Visual Basic for Applications).

 

[thekon]

keusch,
You can make an unbound textbox in the form where you want to see the 'Sum' of your remarks and use this code in form's current event

Private Sub Form_Current()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim str As String

Set rst = db.OpenRecorset("SELECT remark FROM YourTable WHERE lastname='" & Me!lastname & "' AND firstname='" & Me!firstname & "' ORDER BY visitdate&quot

str = ""
While Not rst.EOF
str = str & remark & ". "
rst.MoveNext
Wend
Me![Your_Unbound_TextBox] = str

Set rst = Nothing
Set db = Nothing
End Sub

 

[IFELSETHEN]

Hmmmmm, what happens to your visitdate field if you merge all the remarks to a memo field? Maybe you better create a new table just for the remarks with LastName, FirstName as pointers to the new table.

vb_doc@hotmail.com