Comparing Values in two Tables

[obheron]

Early Morning question to start off the day!

If I have a table with an AutoNumber Primary, and two other columns with text values, can I take a second table compare two of it's columns, compare the text with the main tables two columns and put that particular combinations Primary # from the first table into a Number column in the second?

Ex. I have a table with two columns of fruit:

| 1 | Apple | Orange |
| 2 | Apple | Pear |

The second table has all the relationships of kinds of fruit:

| | Apple | Orange | Seeds |
| | Apple | Orange | Skins |
| | Apple | Pear | Seeds |
| | Apple | Pear | Skins |

I want to match cases if you will of columns 1 and 2 between the tables and populate the lower tables first column with the Number associated with the combination in the second column.
I am trying to find a way to organize these two tables in such a fashion for an input form for more relationships. So if I have two combo boxes in the form, I want to match those two values in the related table and put the primary key in another field of the form. I am hoping I can do this so that I can avoid multiple numbers for the same pair after user input, i.e. if the user picks Apple and Orange, or Orange and Apple I want the form to assign 1 to the ID number, instead of a new number.

HTH

Thanks.
Obh.

 

[randy700]

How about...

Table1 - fields: ID, Fruit1, Fruit2
Table2 - fields: ID, Fruit1, Fruit2, Description

Query -
UPDATE Table2 SET Table2.ID = Table1.ID WHERE Table1.Fruit1 = Table2.Fruit1 AND Table1.Fruit2 = Table2.Fruit2



Randy

 

[obheron]

Sounds good to me. Where exaclty do I apply the query to? The subform of the form?