Combind Tables in Access Based on Conditions

[dhrehor]

I have come up with the following Script (It does not work)

INSERT INTO Table_1 ( item1, item2, item3, item4, condition_a, item5, item6 )

SELECT Table_2.item1, Table_2.item2, Table_2.item3, Table_2.item4, Table_2.condition_b, Table_2.item5, Table_2.item6
FROM Table_2;

Where table_1.item1 = Table_2.item 1
AND
condition_b < Condition_a




the conditions are numerical.

If condition_b < condition_a then I want that record in Table_1 update with the new condition.

Also if item1 in table 2 does not exist in table 1, then a new entry should be added.

 

[lespaul]

INSERT INTO Table_1 ( item1, item2, item3, item4, condition_a, item5, item6 )

SELECT Table_2.item1, Table_2.item2, Table_2.item3, Table_2.item4, Table_2.condition_b, Table_2.item5, Table_2.item6
FROM Table_2
INNER JOIN Table_1 ON Table_2.Item1 = Table_1.item1

Where condition_b < Condition_a

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

You may try something like this:
UPDATE Table_1 T1 RIGHT JOIN Table_2 T2 ON T1.item1 = T2.item1
SET T1.item1 = T2.item1, T1.item2 = T2.item2
, T1.item3 = T2.item3, T1.item4 = T2.item4
, T1.condition_a = IIf(T2.condition_b<T1.condition_a Or IsNull(T1.condition_a), T2.condition_b, T1.condition_a)
, T1.item5 = T2.item5, T1.item6 = T2.item6;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886