1

I've got the following result in a query, in an Access 2010 database:

Name          Column1       Column2         Column3
-----------------------------------------------------------
Example1          52          447            52447
Example1          52          455            52455
Example1          52          454            52454
Example1          52          453            52453
Example2          100         000            100000
Example2          101         001            999999

I need to transform this data, concatenating each column to a single row, something like:

Name        Column1       Column2                Column3
------------------------------------------------------------------
Example1      52      447,455,454,453    52447,52455,52454,52453
Example2    100,101       000,001            100000, 999999

As I'm still new into Access, I searched for a VBA function for this, but every single function I found disconsidered a point that is important for me:

Some of these rows' columns have more than 255 characters (number of characters summed), so I won't be able to use the functions I found;

Here's a topic that treats a similar case, but it still doesn't match a solution for me:

Microsoft Access condense multiple lines in a table

And this, by Allen Browne: http://allenbrowne.com/func-concat.html

Can someone please help?

Regards,

Community
  • 1
  • 1

1 Answers1

1

I think that the second solution you have found is valid: http://allenbrowne.com/func-concat.html You will need to change the condition because your table has a double key (name, column1)

Your select should be something like this:

SELECT Name, Column1, 
           ConcatRelated("Column2", "MyTable", "Name = '" & [Name] & "' And Column1 = " & [Column1]) as C2, 
           ConcatRelated("Column3", "MyTable", "Name = '" & [Name] & "' And Column1 = " & [Column1]) as C3
    FROM MyTable

Be aware this solution can be very slow for large data

mnieto
  • 3,744
  • 4
  • 21
  • 37
  • It won't... It will concatenate my data to a Text, not to a Memo. –  Dec 16 '13 at 15:42
  • The select statement will run on memory, so data type of columns doesn't matter. If you need to save the results to a table, the field size will be related to the size of the concatenated values. If you have many values, you can't save them in a Text field – mnieto Dec 16 '13 at 16:08
  • I still cant' figure out some way of doing it. –  Dec 17 '13 at 12:01
  • I am getting i syntax error... I am doing this inside a select query, but what is the best way to get this data mined? –  Dec 17 '13 at 12:03
  • I did it! There was some data that i just missed. Thanks for the replys, this code i found fits perfectly :) –  Dec 18 '13 at 18:25