concatenate multiple rows for beginners - help (1 Viewer)

[sponsoraw]

Hi, I know that was already dozen of times, but I can't get it to work. I need something simple (for a beginner like me).
I have simple a simeple table
Report Number and summary
1 abc
1 def
1 fgh
2 jihgf
2 sdfs
3 dfsdf
3 dfgdf

Can someone help me to get
Report Number Summary
1 abc; def; fgh
2 jihgf; sdfs
and so on.....
I've read a lot solution but I can't always understand.

Your help will be much appreciated.
Thanks
Adrian

 

[jzwp22]

The only way to do this with a custom function. Allen Browne has such a function on his website. Here is the link

You would create a new module and a public function in that module. You would then copy Allen's code into that function.

You will then need to call the function (typically in a query) when you need it. You will have to tell the function which table/field it needs to concatenate. In your case, it would be the summary field in your table (not sure what your table name is). You would have to do this in a query that has only 1 record per report number. So you will either need to use a unique query or an aggregate query and then call the function from there.

If the concatenated values from the summary field exceed the 255 character limit of a text field, the result will be truncated.

 

[sponsoraw]

Thanks jzwp22,
It is very likely that I will exceed 255 characters. The summary field is a memo field. Any ideas?
Thanks

 

[jzwp22]

You can use the function with a memo field, but you will still be limited to 255 characters if you call the function in a query.

Displaying the concatenated memo field information in a form can be done using Visual Basic for Application (VBA) code but it would be beyond what a typical beginner would know. The form could only be used in the single form mode; continuous & datasheet modes would not display the information correctly.


I cannot think of any way to do it in an Access report.