Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

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

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
alfamikefoxtrot
Asked:
alfamikefoxtrot
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
can you provide some sample data and expected output.
0
 
alfamikefoxtrotAuthor Commented:
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
 
rquagliaCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Pawan KumarDatabase ExpertCommented:
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
 
aikimarkCommented:
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
 
alfamikefoxtrotAuthor Commented:
Thanks guys -- appreciate the commentary. The Cartesian solution in update query form looks like it's the best after all. Nice and elegant....!
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now