comparing two columns from two different tables

[niki]

Hello,
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
<loop>
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>

Questions:
- 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!
nico

 

[dcx693]

Do you only want to catch exact matches? If so, this is far easier than you think. Database tables (like the main list and the "light" list you have) are usually joined by fields that are equal.

So, in your case, if I had a table (call it table2) of words and I wanted to compare that against table1 that is a much bigger list, all I need to do is create a query where I join the two tables on the word field.

Access will automatically display only those records from both tables where they match up on the word field.

Now this might present a slight problem. Let's say your tables are like this:

table1
bee
apple
orange
zebra

table2
orange
tree

Would you ever have a case where there's a word on your "light" list that wasn't on your main list? If you want to show those, write back and I can tell you how.

Once you've got this query up and running, it's a simple extension of this techique to write the matching entries to another table using an update query.

Do you also want to show the non-matching entries?

 

[niki]

hey thanks for your answer,
I don't think a simple query can be used on this one since I have multiple words in each fields, like this:

Table 1
tree, mountains, river, fields
orange, banana, grape, peach
horse, sheep, cow, dog
(...) *1000
pluto, saturn, mercury, earth

Table 2
card, bus, dog
river, bush, chirac


Since there will be changes in both tables, and that some words in table 2 ("light" table) can not appear in Table 1 for the moment but can appear later on, the solution of the third table can be a good one as it will evolve along with the regular additions made to Tables 1 and 2.
To answer correctly to your question, YES there will be words on my "light" list that won't be on my main list.
By working with a third table, I will also be able to see the non matching entries which I want to be enabled...
Is that enough?
thanks again
Nico

 

[dcx693]

Aha. That makes the solution more complex, but not horribly.

Your data is not in a very usable format (which I'm sure you already know). Somehow, you've got to separate the words out of each of the rows of table2 (the light table). Do that by opening the table using VBA and going through all of the rows in turn, separating the words out of each row and writing the result to a single column in a temporary table. I don't think I have a ready-made function for pulling out the nth string out of a longer string where the words are separated out by commas, but I'm sure they're out there. I'll look for it.

I would do the same thing to table1, but you may not have to. (If you're absolutely sure that a word in table2 could not exist as part of another word in table1, then you only need to split out the words in table2. For example, what if you have the word cow in table2 but the word coward in table1?)

Once you've got both tables with the words separated out, then you can do the simple query I wrote about previously.

 

[Pat Hartman]

This is problem that requires a code solution unless you normalize the tables. The keywords have a many-to-1 relationship to the main tables. They should therefore be stored in a separate table that is linked to the main table.

I am not going to write the code solution for you but the best way to write it is to have an outer loop that reads all the rows of the "long" table and for each row in that table, read each row of the "short" table. Then within that loop, you need a third that loops through the elements of the mushed field in table1, that controls a loop through the mushed fields of table 2 looking for matches. Got that - 4 levels of looping. When a match is found, use .AddNew and .Update to insert a row in your "found" table. Your I/O (Input/Output) costs for this horror are 2000 times x. So if the "short" table has 100 records, you will actually be reading 2000 * 100 = 200,000 records to accomplish this. And for each of these 200,000 records, you'll be looping through x elements of an array, y times.

Don't expect this to run quickly and be very careful to optimize it.

With properly normalized tables, Jet will handle the comparing for you and is optimized to make use of any existing indexes which will dramatically reduce the I/O requirements.

 

[niki]

OOOOKKKKKKK!!!!
I guess I will be wise and use your advices I am working on simplifying my tables.
What is Jet in your last reply? Is it an Access function?
thx

 

[Mile-O]

Jet is the database engine in Access that performs all the actions.

 

[niki]

thanks for the hints...
my problem now comes down to a previous problem. My field with all the keywords, is at the start a field with a lot of non interesting text. Among these text values are the keywords, in this kind of format :"Keywords: moon; earth; pluto ; (...) ; saturn"

So I created a module line which goes like this:
"Public Function TheKeyWords(longText As String) As String
TheKeyWords = Mid$(longText, InStr(1, longText, "Keywords:") + 9)

End Function
"
and which returns to me in single field : "moon; earth; pluto ; (...) ; saturn"

How can I edit it so that it returns to me each word in a single field?
I guess it must be easy but I have been using Access for only two weeks and I don't know the functions...
thx for your help.

 

[jonathandavidb]

Do you only want to catch exact matches? If so, this is far easier than you think. Database tables (like the main list and the "light" list you have) are usually joined by fields that are equal.

So, in your case, if I had a table (call it table2) of words and I wanted to compare that against table1 that is a much bigger list, all I need to do is create a query where I join the two tables on the word field.

Access will automatically display only those records from both tables where they match up on the word field.

Now this might present a slight problem. Let's say your tables are like this:

table1
bee
apple
orange
zebra

table2
orange
tree

Would you ever have a case where there's a word on your "light" list that wasn't on your main list? If you want to show those, write back and I can tell you how.

Once you've got this query up and running, it's a simple extension of this techique to write the matching entries to another table using an update query.

Do you also want to show the non-matching entries?

I started a thread yesterday with the whole of my problem, if you need more backgorund info. I do want to compare two columns from separate tables (databases for that matter) that have mostly the same information, but if they do not than I want to see a NULL value. I don't want to see only the differences, I want to see both full, unfiltered columns, but matched up when there is a match. Thanks.