Concatenating comments from consecutive rows together

[Nick 1136]

Hi,

I have faced an issue which I need help with.

I am trying to do a concatenation of comments on consecutive rows grouped by
a certain ID. I have a list of comments against an id with increasing dates
which I would like to concatenate on to one record per ID as shown below.

What I have in a sub query:
ID Date Comments
1 01/01/2008 Comment 1
1 02/01/2008 Comment 2
1 03/01/2008 Comment 3
2 01/01/2008 Comment 4
3 02/01/2008 Comment 5
3 03/01/2008 Comment 6
3 04/01/2008 Comment 7
3 05/01/2008 Comment 8

Query Output I want
ID CommentsConcatenated
1 Comment 1 - Comment 2 - Comment 3
2 Comment 4
3 Comment 5 - Comment 6 - Comment 7 - Comment 8

I think that this is only possible through adding VBA but am not sure how to
start.

Could anybody provide a structure of how this would be done?
I am using Microsoft Access 97.

Thanks

Nick

 

[Dale Fye]

Check out the function on this web site. I think it will do what you want.

http://www.mvps.org/access/modules/mdl0008.htm