Go Premium for a chance to win a PS4. Enter to Win

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
?
148 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
6 Comments
 
LVL 35

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 35

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

916 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