Unanswered: comparing two columns from two different tables
I have two distinct tables, from these tables I want to compare two fields containing text values (keywords). In table 1, the keywords field contains 2000 rows and each row has about 10 keywords. Table 2 contains a lot less entries.
I would like to create a query which takes one word after the other from the "light" keyword field in Table 2, and which compares these words in a single repetitive manner to the words contained in the keywords field of Table 1. After a "hit" (e.g. a same word contained in both keywords fields from Tables 1 and 2) between the two fields, a report or a third database will be created to inform the user of a matching, giving both keys or identifiers of Tables 1 and 2.
I assume that there are two embedded functions in a single loop as:
there are n words in table 2
for i++, 1<i<n
function 1: reads the first word in keyword field in Table 2 (e.g. sheep)
function 2: searches the entire keyword field in Table 1 for "sheep"
- Being a noob with access, I don't know the functions, neither the syntax of the code to type into my query.
- After that all of the words from a single entry in Table 2 have been identified and searched through Table 1, will there be a problem for Access to keep on searching for the following keywords in the following entry (entries) in the same field?
- What's the best way to show the hits? a printable report or a third database containing the full details of the two keywords matching entries?
thanks for your help!
Thanks for the private mail - sorry I don't reply.
As usual, this proposal is just typed into the forum from memory & is completely untested: another debugging opportunity for you!
I made the following assumptions:
recID1 = long (possibly an autonumber), Primary Key
KeyWd1 = string
recID2 = long (possibly an autonumber), Primary Key
Keywd2 = string
sample data from tbl1:
1; tree, mountains, river, fields, orange, banana, grape, peach
2; fish, horse, sheep
3; house, soup, bread
sample data from tbl2:
1; river, bread
2; soup, orange, fish
result goes into tbl3
recID1 = long
recID2 = long
keywd = string
data in tbl3 based on above sample data
1;1;river (river found in tbl1-record-1 and tbl2-record-1)
1;2;orange ...etc yeah yeah - i got some of them the wrong way round in the first post!
So, assuming your data looks something like my assumptions, and the results in tbl3 are more or less what you are looking for - here we go
Make a table that has the structure of tbl3: your choice if you add an autonumber recID3 - if you will run more than 2*10^9 records through tbl3, the autonumber will slightly complicate your life.
Make a query: select table tbl3: add any one field from tbl3 to the design grid: type * (star) in the criteria line of the grid (Access will change this to Like "*"): in the menu select Query¦Delete query: Save as DELtbl3. When run, this query will delete all records from tbl3
OUTSIDE of any function/subroutine, at the top of the module:
'ici is required by matchKW & stripKW
dim ici as long 'pointer to current position in keywd2
'noKW is a text constant that must NEVER appear in your keywords: choose carefully!
const noKW = "&%#-NONE-#%&"
Private Sub matchKW()
dim dabs as DAO.Database
dim rec1 as DAO.Recordset
dim rec2 as DAO.recordset
dim rec3 as DAO.recordset
dim keys1 as string 'to hold the long set of keywords from tbl1
dim keys2 as string 'to hold the short set of keywords from tbl2
dim aKey2 as string 'to hold an individual keyword from tbl2
docmd.setwarnings false 'turn off Access warning messages
docmd.openquery "DELtbl3" 'delete all entries in tbl3
docmd.setwarnings true 'turn on Access warning messages
set dabs = currentdb
set rec1 = dabs.openrecordset("tbl1")
set rec2 = dabs.openrecordset("tbl2")
set rec3 = dabs.openrecordset("tbl3")
Do While not rec1.EOF
'step through all records in tbl1
keys1 = rec1!keywd1 'get the long keywords list
ici = 1 'initialise the pointer
Do While not rec2.EOF
'step through all records in tbl2
keys2 = rec2!keywd2 'get the short keywords list
aKey2 = stripKW(keys2) 'extract first keyword
Do Until aKey2 = noKW 'until no more keywords
if instr(1, keys1, aKey2) > 0 then 'if matched a keyword
rec3.addnew 'record it in tbl3
rec3!recID1 = rec1!recID1
rec3!recID2 = rec2!recID2
rec3!keywd = aKey2
rec3.update 'save changes
aKey2 = stripKW(keys2) 'extract next keyword
Loop 'while there are still keywords
Loop 'until we hit the end of rec2
Loop 'until we hit the end of rec1
set rec1 = nothing 'tidy up
set rec2 = nothing
set rec3 = nothing
set dabs = nothing
msgbox "Completed", vbinformation, "MatchKW"
Private Function stripKW(someKW as string) as string
'function accepts string someKW containing words separated by commas
'function searches someKW starting at module-wide var ici for...
' a string terminated with a comma
' the residual string in someKW after the last comma
'function returns the string (minus any comma) if found ELSE the module-wide constant noKW
dim tail as long
if ici < len(someKW) then 'if not DONE
tail = instr(ici, someKW, ",") 'find the next comma
if tail = 0 then 'if no comma
stripKW = ltrim$(rtrim$(mid$(somekw, ici))) 'get the residual
ici = len(someKW) +1 'prevent another attempt
else 'there was a comma
stripkw = ltrim$(rtrim$(mid$(somekw, ici, tail-1))) 'get next word
ici = tail + 1 'set the pointer
else 'no commas or residual text left
someKW = noKW 'set the DONE return
don't lose too much time on my work!!! I am not going to ask you for help every time I have a problem like I did before. It's really nice from you to take care of this though, and I really appreciate!