?
Solved

Contantenate records in tables

Posted on 2007-10-01
25
Medium Priority
?
552 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.  
0
Comment
Question by:maximyshka
  • 15
  • 10
25 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 19992658
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
 

Author Comment

by:maximyshka
ID: 19993690
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 19993765

Set db = CurrentDb don't work for you or are you trying to open an different database ?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:maximyshka
ID: 19993845
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 19993913
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
 

Author Comment

by:maximyshka
ID: 19994196
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 19994265
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
 

Author Comment

by:maximyshka
ID: 19994312
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
 

Author Comment

by:maximyshka
ID: 19994346
Although during the run I got error 3021 "No current record"
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 19994527
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
 

Author Comment

by:maximyshka
ID: 19994916
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 19999402
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
 

Author Comment

by:maximyshka
ID: 20000103
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 20000154
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
 

Author Comment

by:maximyshka
ID: 20000976
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
 

Author Comment

by:maximyshka
ID: 20000984
I updated code and included New_LoanID in select statement
0
 

Author Comment

by:maximyshka
ID: 20000996
Debugging points on Second Update Statement.  First Update was OK
0
 

Author Comment

by:maximyshka
ID: 20001037
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 20006223
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
 

Author Comment

by:maximyshka
ID: 20008741
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
 

Author Comment

by:maximyshka
ID: 20008818
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 20009174
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
 

Author Comment

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

.Sort = "New_LoanID"



 
0
 

Author Comment

by:maximyshka
ID: 20009448
 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
 
LVL 48

Accepted Solution

by:
jpaulino earned 2000 total points
ID: 20009545
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question