update one table with data from another

[welshone]

hello,
how can I update table1 with data from table2 where table2.field1 = table1.field1 AND table2.field2 =table1.field2

this is with ms access 2000

thank you

 

[dhookom]

Is a combination of field1 and field2 a primary key in table2? If so create an update query with the two tables joined on the two fields.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[welshone]

no there is no primary key.
however, I think I've asked the wrong question,
this is only one part of it, what I want toachieve is :

if data in table1 can be found in table2 then update the information, if the data in table1 cannot be found in table2 then insert the data.

the tables look like this :

surname dob initial year
jones 12/04/1960 J 2
smith 13/05/1980 R 2

 

[dhookom]

I would create two queries to accomplish this. It would be much easier and take only one query if you could match primary keys (I think every table should have one).

You mentioned two fields for matching. What are they? How do you think you would create an append query and how do you think you would create an update query?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.