SQL INSERT INTO a table using information from two other tables

[Max]

Hi,
I have the following problem. I want to insert records into one table
using the combination of two other tables. Here is a simplified
example: Let's say the first table has a column with RaceCode (which
might have values such as 1,3,5,7, etc), the second table is like a
key for this RaceCode so it has two columns (1st one is 1,3,5,7, etc
and 2nd one with description such as Caucasian, Hispanic, African
American, Native American, etc). Now I want to insert records into the
third table which would look like the first table only instead of
values it will have descriptions but the catch is if the value doesn't
exist in the second table I need to put 'UNKNOWN' How do I accomplish
this? Here is what I have:
Table 1 Table 2 Table 3
-------------------------------------------
CODE CODE DESCRIP DESCRIP
1 1 Cauca Cauca
1 2 Hispa Cauca
2 3 Afri Amer Hispa
7 4 Nati Amer UNKNOWN
2 Hispa
1 Cauca

query = "INSERT INTO table3 (DESCRIP)
SELECT table2.DESCRIP
FROM table1, table2
WHERE table1.CODE = table2.CODE"

The problem is it will not insert the row where the CODE is 7 in
table1 but I want it to be inserted with the value of UNKNOWN. I was
trying to work with IIf function but so far was not successful. Any
help is appreciated!
Thanks.

[Bruce]

ma*****@hotmail.com (Max) wrote in message news:<2c**************************@posting.google. com>...

Hi,
I have the following problem. I want to insert records into one table
using the combination of two other tables. Here is a simplified
example: Let's say the first table has a column with RaceCode (which
might have values such as 1,3,5,7, etc), the second table is like a
key for this RaceCode so it has two columns (1st one is 1,3,5,7, etc
and 2nd one with description such as Caucasian, Hispanic, African
American, Native American, etc). Now I want to insert records into the
third table which would look like the first table only instead of
values it will have descriptions but the catch is if the value doesn't
exist in the second table I need to put 'UNKNOWN' How do I accomplish
this? Here is what I have:
Table 1 Table 2 Table 3
-------------------------------------------
CODE CODE DESCRIP DESCRIP
1 1 Cauca Cauca
1 2 Hispa Cauca
2 3 Afri Amer Hispa
7 4 Nati Amer UNKNOWN
2 Hispa
1 Cauca

query = "INSERT INTO table3 (DESCRIP)
SELECT table2.DESCRIP
FROM table1, table2
WHERE table1.CODE = table2.CODE"

The problem is it will not insert the row where the CODE is 7 in
table1 but I want it to be inserted with the value of UNKNOWN. I was
trying to work with IIf function but so far was not successful. Any
help is appreciated!
Thanks.

You were on the right path. Try:

INSERT INTO table3 ( DESCRIP )
SELECT IIf(IsNull(table2.DESCRIP),"UNKNOWN",table2.DESCRI P) AS MyDescrip
FROM table1 LEFT JOIN table2 ON table1.CODE = table2.CODE

Bruce

[Max Minkov]

Thanks a lot it's just what i needed - works great!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!