compare fields

Hello,
I have two databases which have multiple entries containg each multiple fields, and each of these fields contains a single keyword.


Table 1 (m rows, n keywords for each row)

ID keyword[1] keyword[2] keyword keyword[n]
-----------------------------------------------------------
1 cat dog horse sheep
j ........ .......... ............ ..........
m volvo bmw ferrari renault



Table 2 (p rows, n keywords for each row)

ID keyword[1] keyword[2] keyword keyword[n]
-----------------------------------------------------------
1 spider snake dog fish
j ........ .......... ............ ..........
p audi ferrari ford chrysler

I want access (or maybe excel would be easier), to take each word from one table and to search through the other table for matching. You 'll have understood it, in this example I'd like a third database created with the matches dog and ferrari, as following:

Table 3

ID Keyword match ID entry table 1 ID entry table 2
-----------------------------------------------------------
1 dog 1 1
k ........ .......... ............
p ferrari 2 2

What's the command line, or the function? I believe there will be four loops embeddedlike this: But I don't know the functions nor the declarations to write..
for i=1 to i=m (table1) *chooses the row*
{
for j=1 to j=n (table1) *chooses the keyword in the row*
{ read keyword [j]

for k=1 to k=p (table2) *chooses the row in which the keyword will be searched*
{
for l=1 to l=n (table2) *chooses the keyword in the row with which the keyword will be compared*

{search keyword [j]
}
End for
If keyword [k] [l]=keyword [j]
Return [k] [l] [j]
Else
}
End for
}
End for
}
End for

Another issue is that I have multiple keywords fields for one entry, I guess for calculation purposes I should have a single word for each entry, but I use a parsing query and its made to create a field for each key word... I can have up to 60 keywords for a single entry. How could I use a query on a single field which would create an entry for every keyword?? but hey thats another problem...


Thanks for your help.

Basically use an append and use an inner join query to pull the matched data. You may have multiple queries.
Here is the basic Access SQL to do it:
INSERT INTO Table3 ( Keyword1 )
SELECT Table1.Keyword1
FROM Table2 INNER JOIN Table1 ON Table2.Keyword1 = Table1.Keyword1;

Would take all the matches in Keyword1 in both tables and put that in Keyword1 in the third table.
You can change the join to hook Keyword1 to Keyword2 etc. and run 4 queries (on a combination of 4 keywords fields).

I hope this is what you meant.

hey!
thanks for your answer!!!
you are in the right direction, but the number of keywords and the number of entries is variable!!! When I meant four loops, I meant:
for i=1 to i=m (table1)
{
for j=1 to j=n (table1)
{ read keyword[j]

for k=1 to k=p (table2)
{
for l=1 to l=n (table2)
{search keyword[j]
}
End for
If keyword [k] [l]=keyword[j]
Return [k] [l] [j]
Else
}
End for
}
End for
}
End for

hope this will clear things up!!!
I understand what you wrote in reply, but I thought I was going to use VB code to write a module which would then be used ina query???
thx

Is this a one time deal or ongoing?

I am not sure whether I understand your question or not...
i want this query to work in loops because the databes are updated every week with new keywords. Because of this and of the number of key words (10000 in one table a lot less in the other), I cant type in the search myself, and I want access to automatically perform this search for me...
Is this what you were wainting?

OK, so on going, I would guess you would have to write a VB module. problem as I see it is performance. You are going to do many table scans to come with those matches. OR you might be better off writing a big UNION query to pull all the keywords into one column from the tables, than match those 2 union queries insert the results of that. That is assuming the occurance of each table is static. Something like;
Select keyword1 as Keyword, TableKey as TKey from Tbl1 where keyword1 is not null
UNION
Select keyword2, TableKey from Tbl1 where keyword2 is not null
UNION
Select keyword3, TableKey from Tbl1 where keyword3 is not null

This would remove duplicates (unless you use UNION ALL) and you can just match between the two union queries and insert those that match.

I am sorry but I did'nt understand your reply!!!
what function should I use to do that?
I guess that first, I should use a function which puts all the key words in a single field for both tables
and then compare the two fields of the two tables...
but I don't know the funcitons being a noob!!!!