Appending strings in group summary

[SteveFairclough]

I hope sombody can help with this. I need to create a query based one just one table that will group records by a Reference Number but join the contents of a string field into the group summary. Here's a simple example of what I'm looking for :-

Ref String
1 A
1 B
1 C
2 D
2 E

I want the result to look like this :-

Ref String
1 ABC
2 DE

Anybody have any ideas how to do this with just a query, no VBA ?

I'd be grateful for any assistance.

Thanks


Steve Fairclough
I.T. Manager

 

[jsteph]

This is a somewhat common request. You're looking for a sort of aggregate Concat() function, that would show up with the Sum,Count, etc. It's not a part of any standard sql that I know of, so you'd need to do this manually, in a recordset, and write to a temp table.

My guess is that one reason for it not being a part of sql is that, while the sum(), avg(), count() will all return values that will most likely never overflow it's result data type. A concat'd string could concievably be huge, and the resulting recordset couldn't fit it all.
--jsteph