Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    paris, FRANCE

    Question 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"
    <end loop>

    - 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!

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    Hi Nico,

    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-#%&"
    and then...

    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"
    end sub
    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
    end function

    Last edited by izyrider; 06-17-03 at 14:35.

  3. #3
    Join Date
    Apr 2003
    paris, FRANCE
    hey Izy!!
    thx again for your answer. I'll get through with this one and try not to bother you with small bugs like last time!!
    thx again!

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    already an error:
    stripkw = ltrim$(rtrim$(mid$(somekw, ici, tail-1)))

    should read:
    stripkw = ltrim$(rtrim$(mid$(somekw, ici, tail-1-ici)))

    bonne chance, izy

  5. #5
    Join Date
    Apr 2003
    paris, FRANCE

    Thumbs up

    hey man!
    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!
    thx again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts