if/vlookup in access

[Griffith]

Hi,

I have 2 tables in my database: table1 and table2

The second table contains 2 columns with materials numbers. They are the same materials, but with a different number.

The first table contains 1 column with materials numbers, but they can be found in the 2 columns in the 2nd table, just not the same for every material.

How can I create a 3rd column in the 2nd table that shows something like IF(nr in column1 can be found in table 1; nr1; else nr in column 2)?
So later when I link the 2 tables I need to link on just that one column.

Thanks in advance!

 

[Erick]

I'm not entirely sure I get what you are trying to do.
But if I am to take a stab at what you are trying to do then from what I can understand, you are trying to look up a value that may be in 1 source (maybe a field in 1 table) and if not, then look up another source (maybe another field in a different table), then I think what you need is a query that FULL JOINs your table, where the 'Material' is equal. And then in this query, Field1 will be your material, Field2 is the potential material number from Table1 and Field3 will be the potential material number from Table2. This full join will give you NULL for either Field2 or 3 where there is no matching record. All you then have to do is create a 4th field that says IIF(ISNULL([Field2]),[Field3],[Field2]).