DoCMD Insert Query Conditional

[svcghost]

Hey Guys,

I have an INSERT statement that takes data from two source tables and creates records in a destination third table.

The new table has three fields lets say Name, Row, and Price.
Source table 1 has Name and Row columns.
Source table 2 has Price_A, Price_B, and Price_C



I want to be able to insert Price_A, Price_B, or Price_C of source table 2 DEPENDING on what the Row column value is in source table 1, into the new table.

Does this make sense guys? How would I go about this?

Thank you so much!!

[weekend00]

I want to be able to insert Price_A, Price_B, or Price_C of source table 2 DEPENDING on what the Row column value is in source table 1, into the new table.

there is no Row column value in a table. so you can not match the field [ROW] in table1.

[svcghost]

there is no Row column value in a table. so you can not match the field [ROW] in table1.

I am confused as to what you mean. "Row" is the name of a field in source table 1. Sorry if that's unclear.

[weekend00]

I mean there is no row number in table 2.

[svcghost]

So I cannot create an INSERT statement with an IF statement or something that would, for example, insert the following into the new table?:

t1.Name, t1.Row, t2.Price_A (because Row happens to be row 1) --- using record 1 in table 1, and record 1 in table 2
t1.Name, t1.Row, t2.Price_A (because Row happens to be row 1) --- using record 2 in table 1, and record 1 in table 2
t1.Name, t1.Row, t2.Price_B (because Row happens to be row 2) --- using record 3 in table 1, and record 1 in table 2
t1.Name, t1.Row, t2.Price_A (because Row happens to be row 1) --- using record 4 in table 1, and record 1 in table 2
t1.Name, t1.Row, t2.Price_C (because Row happens to be row 3) --- using record 5 in table 1, and record 1 in table 2
etc..

There is only one record in table 2, but many records in table 1.

[weekend00]

I see what you meant. you are matching table1.row with column of table2.

you can do it but not in good pratice.

We all suggest you modify the structure of table 2 to a two column table like:
Row, price
1 1.0
2 2.2
3 0.3

then you can simply use a join query to create the third table, actually, we do suggest you to create the third table, just save the query for later use.

select table1.name,table1.row,table2.price from table1 inner join table2 on table1.row=table2.row