View original (French)

[ACCESS] Concatenate an entire column

Solved
LaConcatenation -  
 LaConcatenation -

Hello,

I don't know if this should go through SQL or VBA (I’m open to anything), but I'm stuck on something that’s probably simple. When I search, I find more complicated solutions.

I’m ‘simply’ looking to concatenate all the values of a column from a table into a single cell, separated by pipes "|", across an entire column of a query.

Example (column 2 is just there to indicate that I have multiple columns in my table):

I want all the values from column1 of My_Table to be fully concatenated and separated by pipes, in all the cells of column2 of My_Query.

Table: My_Table

Column1         Column2

A                1
B                2
C                3
D                4


Desired result:

Query: My_Query

Column1                   Column2

Data 1 column 1   A|B|C|D
Data 2 column 1   A|B|C|D
Data 3 column 1   A|B|C|D
Data 4 column 1   A|B|C|D

I hope I’ve been clear enough in my request!

Thank you in advance.

2 answers

  1.
    yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassador 1 588
     

    Hello,

    What are you coming across? Have you tested it?

    0
    1. LaConcatenation
       

      Hello,

      If the question is 'have I tried by myself', unfortunately yes, I always try first before asking, otherwise there's no progress :D

      I have tried several things from various forum topics, but I haven't been able to find a topic that meets my needs, so I had to test and tinker each time, but without any results that come close to what I'm looking for..

      Thank you for the attention given to my issue :)

      0
      2. LaConcatenation > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         

        My response seems to have vanished...

        Thank you, your link led me down a path!

        In fact, seeing the different propositions on your link reminded me of a piece of code I had used for something else. I adapted it and after some time and numerous failures, I managed to make it work.

        ublic Function Concatenation() As String Dim res As DAO.Recordset Dim sql As String 'Selects the column Ma_colonne from the table Ma_Table sql = "SELECT Ma_colonne FROM Ma_Table" Set res = CurrentDb.OpenRecordset(sql) 'Concatenates the different records While Not res.EOF Concatenation = Concatenation & res.Fields(0).Value & "|" res.MoveNext Wend 'Removes the last | Concatenation = Left(Concatenation, Len(Concatenation) - 1) 'frees memory Set res = Nothing End Function

        And then I called the function in my query.

        0
      3. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > LaConcatenation
         

        A detail: if the table is empty, line 17 is going to crash, I think.

        2
      4. LaConcatenation > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         

        Indeed, I just cleared my table to test and it was blocking. Thanks for the review! :)

        0
  2. Tessel75
     

    Hello,

    To concatenate with letters or special symbols, the answer is simple. You need to introduce '& "' and '" &' between each element to concatenate. For example: Col1 & " | " & Col2 & " | " & Col3 & " |" & etc.

    To concatenate data that does not belong to the same record, it is simply not possible. This is a characteristic of RDBMS.

    Have a good day.

    0
    1. LaConcatenation
       

      Hello, thank you for your response. However, by using VBA, I was able to meet my needs, see my reply to yg_be above.

      Have a great day

      0