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

Answer 1 / 2
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
 

Hello,

What are you coming across? Have you tested it?
0
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
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > LaConcatenation
 

You are probably wrongly imagining that it's very simple.

Show us what you have tried and what you have obtained, we can undoubtedly help you.

Just in case, you can take inspiration from this: https://stackoverflow.com/questions/54328595/access-vba-concatenate-single-column-query-into-a-single-line-result

1
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
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
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
Answer 2 / 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
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