Appending data into a single field in MS Access

[Sekhar]

Hi,
I have a situation where the input data is as follows
Record-id Text
1000 xxxxxxxxx
1000 yyyyyyyyy
1000 zzzzzzzzz
1010 aaaaaaaaaa
1010 bbbbbbbbbbb
1010 ccccccccccc

Expected result
1000 xxxxxxxxxxx yyyyyyyyyy zzzzzzzz
1010 aaaaaaaaaa bbbbbbbbb cccccccccc

Appreciate your help
Rudra

 

[Roger Carlson]

Assume two tables: Table1 (original) and Table2 (expected result). You
could use code like this:

Sub Denormalize()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim currentID As Integer, previousID As Integer

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Table1") 'table with old format
Set rs2 = db.OpenRecordset("Table2") 'table with new format

rs1.MoveFirst

Do While Not rs1.EOF
currentID = rs1![Record-id]
If currentID <> previousID Then
rs2.AddNew
rs2![Record-id]= rs1![Record-id]
rs2![Text]= rs1![Text]
rs2.Update
Else
rs2.MoveLast
rs2.Edit
rs2![Text]= rs2![Text] & " " & rs1![Text]
rs2.Update
End If
previousID = currentID
rs1.MoveNext
Loop

End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L