Syntax Error Using Update Statement

[mark1110]

I am getting a syntax error when I try to use an update statement. Here is the statement:

UPDATE sales_rep_by_mbu
SET sales_rep_by_mbu.SRRSM = producer_sales_role_codes.sales_role_cd_value
FROM producer_sales_role_codes, sales_rep_by_mbu
WHERE (sales_rep_by_mbu.tin = producer_sales_role_codes.tin AND sales_rep_by_mbu.company_cd = producer_sales_role_codes.company_cd AND
sales_rep_by_mbu.state = producer_sales_role_codes.state AND producer_sales_role_codes.sales_role_cd = "RSM" AND producer_sales_role_codes.mbu_cd = "SR")


When I run this statement, it works fine:

SELECT producer_sales_role_codes.sales_role_cd_value
FROM producer_sales_role_codes, sales_rep_by_mbu
WHERE (sales_rep_by_mbu.tin = producer_sales_role_codes.tin AND sales_rep_by_mbu.company_cd = producer_sales_role_codes.company_cd AND
sales_rep_by_mbu.state = producer_sales_role_codes.state AND producer_sales_role_codes.sales_role_cd = "RSM" AND producer_sales_role_codes.mbu_cd = "SR")

Could someone please help me.


Mark

 

[PWise]

Try this

UPDATE sales_rep_by_mbu
inner join producer_sales_role_codes
on sales_rep_by_mbu.tin = producer_sales_role_codes.tin 
AND sales_rep_by_mbu.company_cd = producer_sales_role_codes.company_cd 
AND sales_rep_by_mbu.state=Producer_sales_role_codes.state AND producer_sales_role_codes.sales_role_cd = "RSM"  
AND producer_sales_role_codes.mbu_cd = "SR"
SET sales_rep_by_mbu.SRRSM = producer_sales_role_codes.sales_role_cd_value

 

[dhookom]

mark1110,
Please post your question in only one forum. Your query must join on primary and foreign keys.

Duane
Hook'D on Access
MS Access MVP

 

[mark1110]

pwise,

I tried the code you suggested and access said the join expression is not supported.


dhookom,

Yes I know I posted this question in multiple forums. I accidently posted it in another forum.