How to concatenate multiple rows into single row

[bs1234]

Hi All,

I'm hoping someone will be able to help me.

I have this table: Detail below in MS Access

ID	Code
123	19
123	37
123	46
456	72
456	93
456	62
456	100
789	66

Where some ID's are repeated but always have a different code, I want to see summarized version as per below:

ID	Code
123	19|37|46
456	72|93|62|100
789	66

Wondering if someone can help?

Many Thanks
Bas

[Bob Fitz]

I don't know of a simple way to do that.
Do a search on the Allen Browne site for "concatenate". It might give you some ideas on how it may be done with a user defined function

[OBP]

What you are trying to do is actually "Transpose" (An Excel Paste Function) not concatenation.
There is a "Transform function in SQL that should do it, but I have never used it, you can also use a combination of a multi Union Query with a Crosstab query, which I have also not used.
The way that I would approach this in VBA is to use a Temporary table with fields (say field1 to field20) already set up and then populate that from the original table by iterating through the records in a loop.