Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access - loop through contents of table and iteratively find/replace words from second table

Posted on 2016-11-27
6
Medium Priority
?
132 Views
Last Modified: 2016-11-27
I have two tables, Table1 and Table2. Table1 has a column called Dump which contains a bunch of string data. I want to iteratively go through the string data in Dump and remove certain reference words that exist in Table2 (these words exist in a column called Word). I'd like to do this programmatically (seems cleaner) but am open to doing it in a query.  I keep getting a variety of errors depending on what I change -- ranging from "member not found" to "two few parameters." Here's the code as it exists now.  
    Dim dbs As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim iDataID As String
    Dim WordData As String

 
    Set dbs = CurrentDb
    Set rs1 = dbs.OpenRecordset("SELECT [Table1].[Dump] FROM Table1")
    Set rs2 = dbs.OpenRecordset("SELECT [Table2],[Word] FROM Table2")
 
  
 rs2.MoveFirst
    Do While Not rs2.EOF
        
         iDataID = rs2.Fields("Word").Value
        WordData = rs1.Fields("Dump").Value
 
        rs1.MoveFirst
        Do While Not rs1.EOF
            
        WordData = Replace(rs1.Fields("Dump"), iDataID, " ")
         
             rs1.MoveNext

        Loop

            rs1.MoveFirst
 
        rs2.MoveNext
    Loop
 
    rs1.Close
    rs2.Close
 
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set dbs = Nothing

Open in new window

I could probably solve this over the next few knuckleheaded days but seems better to ask the experts. Feedback welcomed.

Edit: Code placed in snippet box - GrahamSkan
0
Comment
Question by:alfamikefoxtrot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41903135
can you provide some sample data and expected output.
0
 

Author Comment

by:alfamikefoxtrot
ID: 41903140
Sure --

Original contents of Table1:

ID      Dump
1      this is a test. this is only a test.
2      the quick brown fox jumps over the lazy dog.


Contents of Table2:

ID      Word
1      the
2      this


I want the code to be able to remove from Table1.Dump the words that appear in Table2.Word, resulting in the contents of Table1 now looking like:

ID      Dump
1      is a test. is only a test.
2      quick brown fox jumps over lazy dog.


Of note -- the contents of Table 2 will vary (e.g., different words can be present in table) and there can be anywhere from a couple to 20 or 30 that would need to iteratively be removed.
0
 
LVL 1

Assisted Solution

by:rquaglia
rquaglia earned 100 total points
ID: 41903156
hi!
in the main loop try:
==========
        rs1.MoveFirst
        Do While Not rs1.EOF

        WordData = Replace(rs1.Fields("Dump"), iDataID, " ")
        rs1.edit                                        ' begin record editing
        rs1!dump = wordData              ' modify field
        rs1.update                                  ' update record with new field value
        rs1.MoveNext

        Loop
========
So you can edit the record you are analyzing.
Good work!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 30

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 100 total points
ID: 41903163
Try..

CREATE TABLE MSTable1
(
	 ID   INT   
	,Dump VARCHAR(1000)
)


INSERT INTO MSTable1 VALUES
(1     , 'this is a test. this is only a test.'),
(2     , 'the quick brown fox jumps over the lazy dog.')



CREATE TABLE MSTable2
(
	 ID   INT   
	,Word VARCHAR(1000)
)


INSERT INTO MSTable2 VALUES
(1    ,  'the'),
(2    ,  'this')


SELECT t.aID , t.NewCol AS Dump 
FROM 
(
	SELECT a.Dump , a.ID AS aID, b.ID , b.Word , REPLACE(Dump,Word,'') AS NewCol 
        FROM MSTABLE1 AS a , MSTABLE2 AS b
) AS t INNER JOIN MSTABLE1 AS K ON k.Dump <> t.NewCol AND k.ID = t.ID
ORDER BY t.aID

--

Open in new window


Output

aID         Dump
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1            is a test.  is only a test.
2            quick brown fox jumps over  lazy dog.

Open in new window


Hope it helps !!
0
 
LVL 46

Accepted Solution

by:
aikimark earned 1800 total points
ID: 41903193
You can do the update with a single Access query:
Update Q_28985668_Table1 
    Inner join Q_28985668_Table2 on [Q_28985668_Table1].Dump Like "*" & [Q_28985668_Table2].Word & "*"
Set [Q_28985668_Table1].Dump = Replace([Q_28985668_Table1].Dump, [Q_28985668_Table2].Word, "")

Open in new window

1
 

Author Closing Comment

by:alfamikefoxtrot
ID: 41903261
Thanks guys -- appreciate the commentary. The Cartesian solution in update query form looks like it's the best after all. Nice and elegant....!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

721 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