concatenate rows from same column

[vojinb]

Hi,
does anybody know how to concatenate two rows from same column where second row length is smaller then first row length.
I want to concatenate them and insert it to new column like

column1
xxxxxxxxxxxxx
yyyy
zzzzzzzzzzzzzzz
qqqqq
vvvvvvvvvvvvvvv
ssssss

where result of column 2 would be
xxxxxxxxxxxxyyyyyy
zzzzzzzzzzzzzzzqqqqq
vvvvvvvvvvvvvvssssss

I would really appreciate if someone could help me. I know that I can do it by exporting it to excel and do simple concatenate formula and back import it again, but I want to do it in access, but I cannot find on internet anything that could help me.

 

[PeterF]

If there's only one column the result will be unpredictable in a database as the sort order is unkown (and why would you do it).
If it's to concatenate the records based on other field(s) in the record you could try a crostabquery.

 

[vojinb]

It's not related on other field. It is table with values imported from some stupid txt file and second line is very imported part of name of a row above.
In column I have

ID (auto number) Name
1 xxxxxxxxxxxxxx
2 yyyyy
3 zzzzzzzzzzzzzzzz
4 qqqqq
5

Could it be to somehow read column "name" and when it gets to "name" having less then 20 characters to concatenate it to row above based on ID's?

 

[Taruz]

Hi..

This query, gets the value of the next record and combines..:

select 
           iif(
                len([name]) >len([nextr]) ,[name] & [nextr],
                                                                                          "") 
from (
           select 
 name, 
 dfirst("name","table_name","name<> '" & [name] & "' and ID>=" & [ID]) as nextr
from table_name
                                 )  as trz
where iif(
                len([name]) >len([nextr]) ,[name] & [nextr],
                                                                                          "") <>""

[ID],[name] and [table_name] sections, table and fields names change with

 

[Taruz]

How can I put this code?
Could it be as currentdb,execute?

select iif(len([name]) >len([nextr]) ,[name] & [nextr],"") from (select name, dfirst("name","table_name","name<> '" & [name] & "' and ID>=" & [ID]) as nextrfrom table_name) as trz where iif(len([name]) >len([nextr]) ,[name] & [nextr],"") <>""

Open a query in sql view and paste.. then run the query

 

[vojinb]

sorry stupid question

 

[Taruz]

no problem..


Have you tried?..

 

[vojinb]

It's still working,
table has 107 000 records and here is example of name that should be concatenated :

LD.1.TR.CHF.21012.3000.1400.Y.YU.YU.16
05.T.SHORT
AC.1.TR.EUR.6001.1000.2100.N.SE.SE.160
501

and so on for 107 000 times )

 

[vojinb]

Thanks, the query is working, but when I try to result insert into new column, it gives me no result,here is query
INSERT INTO mytable ( name_full )
SELECT IIf(len([name])>len([nextr]),[name] & [nextr],"")
FROM (SELECT name, dfirst("name","mytable","name<> '" & [name] & "' and ID>=" & [ID]) AS nextr FROM mytable) AS trz
WHERE IIf(len([name])>len([nextr]),[name] & [nextr],"")<>"";

What could be the problem?

 

[Taruz]

Thanks, the query is working, but when I try to result insert into new column, it gives me no result,here is query
INSERT INTO mytable ( name_full )
SELECT IIf(len([name])>len([nextr]),[name] & [nextr],"")
FROM (SELECT name, dfirst("name","mytable","name<> '" & [name] & "' and ID>=" & [ID]) AS nextr FROM mytable) AS trz
WHERE IIf(len([name])>len([nextr]),[name] & [nextr],"")<>"";

What could be the problem?

insert query is not , update will be the most logical..:

update (
select iif(len([name])>len([nextr]),[name] & [nextr],"") as ttt, ID
from (select 
 name, 
 dfirst("name","mytable","name<> '" & [name] & "' and ID>=" & [ID]) as nextr, ID
from mytable
                                 )  as trz
where 
iif(len([name])>len([nextr]),[name] & [nextr],"")<>"" ) as tt 
    
inner join mytable on tt.ID = mytable.ID set mytable.name_full = [ttt]

another suggestion:

for faster, this also can use..:

Dim rs As DAO.Recordset
Dim trz As String

Set rs = CurrentDb.OpenRecordset("select * from mytable order by ID desc , len([name])")

With rs
trz = !Name
    Do Until .EOF
        If Len(!Name) > Len(trz) Then
            .Edit
            !name_full = !Name & trz
            .Update
        Else
            trz = !Name
        End If
        .MoveNext
    
    Loop
    
End With

Set rs = Nothing

''delete to second(short) record
'CurrentDb.Execute "delete from mytable where [name_full] is null"

 

[vojinb]

second code you gave is really fast but in new column(name_full) it gives me same records I have in column name, without concatenation,just like it was copied

 

[Taruz]

select * from mytable order by ID desc , len([name])

Test your sorting in.. long data, should be first .. After a short..

 

[vojinb]

yes,they are but...

 

[vojinb]

Hi,
first thanks for your support. From two codes you gave me first one is working but it takes hours to update table.
Second one is extremely fast comparing to first,but not doing concatenation with condition settings like in code, if I change the sign < >, or desc It does do update but in wrong way.In the way as it is it just doing copy of one column to another,
For last hours I'm trying to figure out the problem, but....
In attachment I put reduced table with your code if you could help me what is wrong?
For example for records in column "name":
row1: AC.1.TR.CHF.5001.1000.1800.N.CH.CH.150
row2: 4...
the result in column name_full should be:
row1:AC.1.TR.CHF.5001.1000.1800.N.CH.CH.1504...

Code is working in any other way except one I need
Thanks

 

[Taruz]

Hi,
first thanks for your support. From two codes you gave me first one is working but it takes hours to update table.
Second one is extremely fast comparing to first,but not doing concatenation with condition settings like in code, if I change the sign < >, or desc It does do update but in wrong way.In the way as it is it just doing copy of one column to another,
For last hours I'm trying to figure out the problem, but....
In attachment I put reduced table with your code if you could help me what is wrong?
For example for records in column "name":
row1: AC.1.TR.CHF.5001.1000.1800.N.CH.CH.150
row2: 4...
the result in column name_full should be:
row1:AC.1.TR.CHF.5001.1000.1800.N.CH.CH.1504...

Code is working in any other way except one I need
Thanks

Hi again..

Try this..:

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim tt As String
Set rs = CurrentDb.OpenRecordset("select * from bilansuspjeha " _
& " where name Not Like '*' & 'bank' & '*' and Len([name])>2 and left([name],1)='' ")
With rs

    Do Until .EOF

On Error Resume Next
.MoveNext
        tt = !Name
.MovePrevious
.Edit
        If !Name <> tt And Len(tt) < 20 Then
            
            !name_full = RTrim(!Name) & Trim(tt)
            
        Else
        !name_full = !Name
            
        End If
.Update
tt = !Name
.MoveNext
    
    Loop
    
End With

Set rs = Nothing
Me.bilansuspjehasubform2.Requery
End Sub

 

[vojinb]

Thanks )))))))
You saved me ))
It'sworking and it is excelent!
Thanks once again

 

[Taruz]

I'm glad that I helped you..