We help IT Professionals succeed at work.

Contantenate records in tables

649 Views
Last Modified: 2013-11-27
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.  
Comment
Watch Question

Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

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
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:

Set db = CurrentDb don't work for you or are you trying to open an different database ?

Author

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>>
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

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
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

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

Author

Commented:
Although during the run I got error 3021 "No current record"
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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 ?

Author

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



Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

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
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
The New_LoanID isn't pulled by the SQL. You should inclued im.

testnewstring = "SELECT [Test-Delinquent].Tax_Explanation,New_LoanID  FROM [Test-Delinquent];"

Author

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 & "'")

Author

Commented:
I updated code and included New_LoanID in select statement

Author

Commented:
Debugging points on Second Update Statement.  First Update was OK

Author

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
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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)

Author

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

Author

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 = ""
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

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

.Sort = "New_LoanID"



 

Author

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
IT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.