Update table where criteria depends on other table column for Access 2000 [RESOLVED]

i got 2 table..table1, table2(which is like the duplicate for table2 for specific reason in my company)

table1 = col1 + qty
table2 = date_now + col1 + qty

every month table2's content is change according to what table1 has..but somehow (not sure why it happens) it only inserted date_now and col1..

so now i need to update table2.qty where the table2.col1 = table1.col1..
is it possible? can't figure it out what the syntax are

thx

Are you looking for something like...?

VB Code:

INSERT INTO Table2... SELECT * FROM Table1

thx dee-u but that's not what i'm mean

ex: in t1
col1 - col2
a - 1
b - 2
c - 3

in t2
col1 - col2
a - 0
b - 0

the update need to make t2 something like this
col1 - col2
a - 1
b - 2

since c not found in t2 then leave it alone

hope it makes sense

erickwidya:

Maybe I am not understanding you correctly, but isn't it just a matter of doing a SQL UPDATE?

Looks like you almost wrote it in your posting:

Quote:

---

update table2.qty where the table2.col1 = table1.col1

---

Is very close.

The syntax for a SQL UPDATE is:
UPDATE table_name
Set column = value
WHERE condition

So, you need something like:

UPDATE table2
SET qty = qty
WHERE table2.col1 = table1.col1

I may be a little bit off, but I think this should get you pretty close.

The SET statment might need to be:
SET qty = table1.qty

Good Luck

Quote:

---

UPDATE table2
SET qty = qty
WHERE table2.col1 = table1.col1

---

-> aaa..u cheat, u use my logic :bigyello: , why MS doesn't developt this kind of magic ;)

just try it, and it work..never thought about it until i saw urs

Code:

---

UPDATE table2 INNER JOIN table1 ON table2.col1 = table1.col1
SET table2.qty = table1.qty

---

-> i don't know it can use JOIN huaa...

thx again for the insight AIS4U