Thanks!  I got it worked out now.  I agree, it's a pain the way I was trying to do this.

Don

I right-click on the Table1 and select "Open table" and then click the SQL button in the toolbar.  I modify the query and check syntax and it rewrites my query...

This was an accidental post.  See other post with same name.  I could not find a delete to remove this one.

Don

When I try the format by Matt Miller and check syntax the query gets rewritten as follows:

UPDATE    t1

SET              t1.<field1> = t2.<field2>

FROM         <Table1> AS t1 INNER JOIN

                      <Table2> AS t2 ON t1.<FKField> = t2.<PKField> CROSS...

I tried that and every record I was updating had exactly the same value.  It's like it's trying to do a cross join somehow.

Don

The Where statement is based on the Primary key <PKField> and a Foreign key <FKField> combination so there can only be ONE value returned in the sub-select.

I don't understand why the 2005 version...