Contantenate records in tables

Would you please tell me is there a way using VBA code and queries to get data from one table and contantenate in another table in following way:

table 1:

Loan No      Comments
123456       Hi Peter
123456       Hi Martin
123456       Hi Nick

I need to put Comments in following way: "Hi Peter; Hi Martin; Hi Nick"

As you understand actual data is different.  Loan have multiple comments sometimes up to 4 or 5 records.
I need to contantenate all comments into one record (which has memo data type)

All these code will be run from the command button of the form.

I was asking this questions before however experts could not help me.  
maximyshkaAsked:
Who is Participating?
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
Ok, I will try to explain you:

If you sort the values by the ID then you will have all them followed (1,1,2,2,2,3,3,3,3,3,4,4,5,5, &) With this you can start in the first collect the description of all the equal IDs and loop until you find a new ID. If you find a new ID different then empty (here is the else command) you will update all the those IDs (Update command), clean the used variables and start a new loop until you will find a new ID.

To check better how this works add an breakpoint in  Do Until .EOF, using F9 and go step by step in the code using the F8.

PS: .Sort = "New_LoanID" it sorts doesn't group!
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You can do something like this

Dim db As Database
Dim dbGrupo As Recordset
Dim SQL As String
Dim result As String

Set db = CurrentDb
SQL = "SELECT * FROM YourTableName;"
Set dbGrupo = db.OpenRecordset(SQL)
With dbGrupo
    .MoveFirst
    Do Until .EOF
         result = result & .Fields("Comments") & ";"
        .MoveNext
    Loop
    .Close
End With
MsgBox result
0
 
maximyshkaAuthor Commented:
Not working.  This is the code.  Error somewhere within from clause.  Would you please help me


Public Sub Command17_Click()
Dim testmycon As DAO.Database
Dim testmyrst As DAO.Recordset
Dim testmystring As String
Dim teststrfinalstring As String
Dim testtaxdueinput As String
Dim testnewstring As String

Set testmycon = DBEngine.OpenDatabase("C:\Documents and Settings\E090MY1\Desktop\ESC Code Work - 9-13-07\EscCode-working copy.mdb")
testnewstring = "select Tax_Explanation from Delinquent_file;"
Set testmyrst = testmycon.OpenRecordset(testnewstring)
With testmyrst
    .MoveFirst
    Do Until .EOF
        teststrfinalstring = teststrfinalstring & .Fields("Tax_Explanation") & ";"
       .MoveNext
    Loop
    .Close
End With
MsgBox teststrfinalstring




testmyrst.Close
testmycon.Close

Set testmycon = Nothing

End Sub
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Jorge PaulinoIT Pro/DeveloperCommented:

Set db = CurrentDb don't work for you or are you trying to open an different database ?
0
 
maximyshkaAuthor Commented:
Sorry, previous comments were misleading. Program is working I can't get result in message box because there are over 260 records.  I got some mistake "There's not enough free memory to update display.

Part that I put in quotes is working.  Now I need to put value of that result into the table
<<So, how can I update table putting the result into table (value of variable result or strfinalstring)
Public Sub Command17_Click()
Dim testmycon As DAO.Database
Dim testmyrst As DAO.Recordset
Dim testmystring As String
Dim teststrfinalstring As String
Dim testtaxdueinput As String
Dim testnewstring As String

Set testmycon = DBEngine.OpenDatabase("C:\Documents and Settings\E090MY1\Desktop\ESC Code Work - 9-13-07\EscCode-working copy.mdb")
testnewstring = "select Tax_Explanation from Delinquent_file;"
Set testmyrst = testmycon.OpenRecordset(testnewstring)
With testmyrst
    .MoveFirst
    Do Until .EOF
        teststrfinalstring = teststrfinalstring & .Fields("Tax_Explanation") & ";"
       .MoveNext
    Loop
    .Close
End With>>
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You can use the same method, just open the recordset of the table you like to add the new file and do this:

' For add new
With testmyrst
        .AddNew
        .Fields(YourFieldName) = teststrfinalstring
       .Update
       .Close
End With

' For update
With testmyrst
        .Edit
        .Fields(YourFieldName) = teststrfinalstring
       .Update
       .Close
End With

0
 
maximyshkaAuthor Commented:
Sorry, how can I combine 2 parts:

Purpose.  I'm trying to update same delinquent file, but instead of old value of the "Tax_Explanation" put new value of updated field  (teststrfinalstring) which should have contantenation of all records (first part)
first:

Set testmycon = DBEngine.OpenDatabase("C:\Documents and Settings\E090MY1\Desktop\ESC Code Work - 9-13-07\EscCode-working copy.mdb")
testnewstring = "select Tax_Explanation from Delinquent_file;"
Set testmyrst = testmycon.OpenRecordset(testnewstring)
With testmyrst
    .MoveFirst
    Do Until .EOF
        teststrfinalstring = teststrfinalstring & .Fields("Tax_Explanation") & ";"
       .MoveNext
    Loop
    .Close
End With

with second part

With testmyrst
        .Edit
        .Fields("Tax_Explanation") = teststrfinalstring
       .Update
       .Close
End With




testmyrst.Close
testmycon.Close

Set testmycon = Nothing

End Sub
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Sorry I didn't understand it right( I'm Portuguese!). You want to update the same table with the result (each record) ?

Is this that you want, to update the first record ?

Set testmycon = DBEngine.OpenDatabase("C:\Documents and Settings\E090MY1\Desktop\ESC Code Work - 9-13-07\EscCode-working copy.mdb")
testnewstring = "select Tax_Explanation from Delinquent_file;"
Set testmyrst = testmycon.OpenRecordset(testnewstring)
With testmyrst
    .MoveFirst
    Do Until .EOF
        teststrfinalstring = teststrfinalstring & .Fields("Tax_Explanation") & ";"
       .MoveNext
    Loop
     .MoveFirst
     .Edit
     .Fields("Tax_Explanation") = teststrfinalstring
    .Update
    .Close
End With
0
 
maximyshkaAuthor Commented:
As you unerstand I do have 260 records of the loans.  Each record contains 2,3 or more lines of comments.  I do want in one shot update comments for all loans.  So, instead of 2, 3 or more lines of comments each loan should have 1 updated line of comment.  Example I posted when I asked the question:

 <need to put Comments in following way: "Hi Peter; Hi Martin; Hi Nick">

**Thank you very much for your help
0
 
maximyshkaAuthor Commented:
Although during the run I got error 3021 "No current record"
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
So you have to delete some records. Sorry

You have this
1 Hi Peter
2 Hi Martin
3 Hi Nick
4 ...

And you want this
1 Hi Peter; Hi Martin; Hi Nick

 and the record 2,3,4 ... you delete them ?
0
 
maximyshkaAuthor Commented:
When I will put comments from the second, third, fourth record to the first, records will be duplicated, I will get in the end something like
Loan No   Comments
123456    1 Hi Peter; Hi Martin; Hi Nick
123456    1 Hi Peter; Hi Martin; Hi Nick
123456    1 Hi Peter; Hi Martin; Hi Nick

654123    Hi Martin; Hi Nick; 1 Hi Peter;
654123   Hi Martin; Hi Nick; 1 Hi Peter;
654123   Hi Martin; Hi Nick; 1 Hi Peter;

So, you see I'm trying to combine information from three records to the one and then remove duplicated records

Then I can remove them



0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Sorry maximyshka I didn't had time yesterday.

Try this code (after your ajustments)

Dim db As Database
Dim dbGrupo As Recordset
Dim SQL As String
Dim result As String
Dim currentID As String

Set db = CurrentDb
SQL = "SELECT * FROM table1;"
Set dbGrupo = db.OpenRecordset(SQL)
With dbGrupo
    .Sort = "ID"
    .MoveFirst
    Do Until .EOF
        If currentID = .Fields("ID") Or currentID = "" Then
            currentID = .Fields("ID")
            result = result & .Fields("text") & ";"
            .MoveNext
             If .EOF Then
                CurrentDb.Execute ("UPDATE table1 SET [text] = '" & result & "' WHERE [ID] = '" & currentID & "'")
                currentID = ""
                result = ""
            End If
        Else
           CurrentDb.Execute ("UPDATE table1 SET [text] = '" & result & "' WHERE [ID] = '" & currentID & "'")
           currentID = ""
           result = ""
        End If
    Loop
    .Close
End With
0
 
maximyshkaAuthor Commented:
Thank you for your reply.  I made adjustments and tried the code.  First, I got run-time error: that I have an error in From Clause.  After I copied sql syntax to the code, I've got following run-time error:

run-time error 3265 item not found in this collection

  <<If currentid = .Fields("New_LoanID") Or currentid = "" Then>>  This line is highlighted.

I paste changed code below.

Q: Did you tried anything on your local machine.  Should I add anything in library?



Private Sub Command12_Click()
Dim testmycon As DAO.Database
Dim testmyrst As DAO.Recordset
Dim testmystring As String
Dim teststrfinalstring As String
Dim testtaxdueinput As String
Dim testnewstring As String
Dim currentid As String

Set testmycon = DBEngine.OpenDatabase("C:\Documents and Settings\Mike\Desktop\ESC Code Work - 9-13-07\EscCode-working copy 2007.mdb")
testnewstring = "SELECT [Test-Delinquent].Tax_Explanation FROM [Test-Delinquent];"
Set testmyrst = testmycon.OpenRecordset(testnewstring)
With testmyrst
    .Sort = "New_LoanID"
    .MoveFirst
    Do Until .EOF
        If currentid = .Fields("New_LoanID") Or currentid = "" Then
        currentid = .Fields("New_LoanID")
        teststrfinalstring = teststrfinalstring & .Fields("Tax_Explanation") & ";"
       .MoveNext
        If .EOF Then
                CurrentDb.Execute ("UPDATE Test-Delinquent SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = '" & currentid & "'")
                currentid = ""
                teststrfinalstring = ""
            End If
        Else
                CurrentDb.Execute ("UPDATE Test-Delinquent SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = '" & currentid & "'")
                currentid = ""
                teststrfinalstring = ""
        End If
   
        Loop
        .Close
        End With
       End Sub
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
The New_LoanID isn't pulled by the SQL. You should inclued im.

testnewstring = "SELECT [Test-Delinquent].Tax_Explanation,New_LoanID  FROM [Test-Delinquent];"
0
 
maximyshkaAuthor Commented:
Thank you.

Now Run-Time Error: Something wrong in Update Statement

CurrentDb.Execute ("UPDATE Test-Delinquent SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = '" & currentid & "'")
0
 
maximyshkaAuthor Commented:
I updated code and included New_LoanID in select statement
0
 
maximyshkaAuthor Commented:
Debugging points on Second Update Statement.  First Update was OK
0
 
maximyshkaAuthor Commented:
CurrentDb.Execute ("UPDATE [Test-Delinquent] SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = '" & currentid & "'")
                currentid = ""
                teststrfinalstring = ""

When I put Name of the Table in Brackets ([Test-Delinquent] i got following error:
Run-Time Error 3464 Data Type Mismatch in Criteria Expression
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
If currentid is numeric then change to this (my text I use it an text field)

CurrentDb.Execute ("UPDATE [Test-Delinquent] SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = " & currentid)
0
 
maximyshkaAuthor Commented:
Man you are great.  Looks like it is working.  I will go over the code and everything is working fine I will ask moderator to give you additional 500 points because work was extra hard.

Here's final code

Private Sub Command17_Click()
Dim testmycon As DAO.Database
Dim testmyrst As DAO.Recordset
Dim testmystring As String
Dim teststrfinalstring As String
Dim testtaxdueinput As String
Dim testnewstring As String
Dim testnewcount As Double
Dim currentid As String


Set testmycon = DBEngine.OpenDatabase("C:\Documents and Settings\E090MY1\Desktop\ESC Code Work - 9-13-07\EscCode-working copy.mdb")
testnewstring = "SELECT [Test-Delinquent].Tax_Explanation, [Test-Delinquent].New_LoanID  FROM [Test-Delinquent];"
Set testmyrst = testmycon.OpenRecordset(testnewstring)
With testmyrst
    .Sort = "New_LoanID"
    .MoveFirst
    Do Until .EOF
        If currentid = .Fields("New_LoanID") Or currentid = "" Then
        currentid = .Fields("New_LoanID")
        teststrfinalstring = teststrfinalstring & .Fields("Tax_Explanation") & "; "
       .MoveNext
        If .EOF Then
                CurrentDb.Execute ("UPDATE [Test-Delinquent] SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = " & currentid)
                currentid = ""
                teststrfinalstring = ""
            End If
        Else
                CurrentDb.Execute ("UPDATE [Test-Delinquent] SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = " & currentid)
                currentid = ""
                teststrfinalstring = ""
        End If
   
        Loop
        .Close
        End With
       End Sub

0
 
maximyshkaAuthor Commented:
I'm trying to understand the logic.  Please tell me where I'm wrong and would you please answer questions

1)Sort Loans by Loan ID
2)Go to the First Record
3) Go to the Loan Number put current New Loan Number into variable currentid

 If currentid = .Fields("New_LoanID") Or currentid = "" Then
(In this statement why you also select part currentid = "")--how empty string goes?

4) Get value of strfinalstring into test strfinalstring.  Do it until the end of recordset

Not really clear how this part is working (What is the purpose of assigning empty string to currentid and teststrfinalstring)
If .EOF Then
                CurrentDb.Execute ("UPDATE [Test-Delinquent] SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = " & currentid)
                currentid = ""
                teststrfinalstring = ""
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Thanks maximyshka.

3) If currentid = .Fields("New_LoanID") Or currentid = "" Then
As you can see it later I set the variable to empty (currentid = ""). So if it's empty it's a new ID or if it is the same ID you add it to the variable teststrfinalstring

4)
If you don't clear the teststrfinalstring after the update command you will have all the values on it and you only want the values from that ID. So after the update you clear the variables for a new ID - start a new cycle.

Hope I where clear. If you have any other douth just say it.

jpaulino
0
 
maximyshkaAuthor Commented:
1)Sorry, with this command you sorting or you grouping by Loan Number (New_LoanID)

.Sort = "New_LoanID"



 
0
 
maximyshkaAuthor Commented:
 If .EOF Then
                CurrentDb.Execute ("UPDATE [Test-Delinquent] SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = " & currentid)
                currentid = ""
                teststrfinalstring = ""
            End If
        Else
                CurrentDb.Execute ("UPDATE [Test-Delinquent] SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = " & currentid)
                currentid = ""
                teststrfinalstring = ""
        End If

What is the purpose of Else part here.  To me you running absolutely the same query.  Could you run this query only once: when recordset end of file run update statement.  Sorry Else Statement little bit confusing.
<<<<
  Else
                CurrentDb.Execute ("UPDATE [Test-Delinquent] SET [Tax_Explanation] = '" & teststrfinalstring & "' WHERE [New_LoanID] = " & currentid)
                currentid = ""
                teststrfinalstring = ""
>>>>
        End If
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.