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.