Solved

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

Posted on 2016-11-27
6
113 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 29

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 25 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 25 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 450 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…
Suggested Courses
Course of the Month8 days, 22 hours left to enroll

615 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