Trying to create update query

[Buddha1]

I am trying to create an update query that will update Tbl B info with a field from Tbl A. Example:
Tbl A contains the following fields: Site Batch# Date
Tbl B contains the following fields: Site Date Batch Acct#
I want to update the batch field in Tbl B with the batch number in Table A. I want the field to update only those fields whose date and site match in both tables. Can someone help me with this?

 

[Tranman]

Here you go, Buddha1:

UPDATE A INNER JOIN B ON (A.date = B.date) AND (A.site = B.site) SET B.batch = [A].[batch];

Tranman

 

[dhookom]

This update may depend on the combination of Date and Batch being a unique index in table A.

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

 

[Tranman]

Duane,
You mean Date and Site, right? I guess I assumed that Buddha1 would have considered and eliminated the possibility of duplicate rows.

"I want the field to update only those fields whose date and site match in both tables."

Tranman

 

[dhookom]

Tranman,
Good catch, I did mean Site. If there was a many to many match, then the query would fail. Also, even though the fields might be unique, Access might not run the query unless the INDEX is created.

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