Simple update Query fails between two tables in the same Access 2007 database

[NewMexicoNovice]

Colleagues,



I am trying to run a simple update query between two tables with identical field names in the same Access 2007 database (.accdb). The purpose of this query is to change the names of employees in the event they marry, etc.

(As a simple test, I've been trying to change Smith, Bob to Smiths, Bob.)

Sheet1 is the table with the new employee information. EEInfo is the table with old employee info. I am using Query Design to select the Update Query and here is a copy of the simple code in SQL View:

UPDATE EEInfo INNER JOIN Sheet1 ON EEInfo.[Rep Name] = Sheet1.[Rep Name] SET Sheet1.[Rep Name] = [EEInfo].[RepName].

(Rep ID is the primary key between the two tables)

Once I press run, I do not receive any syntax error message. Instead I receive the expected "You are about to update 2608 rows." However, there is no update to the desired Smiths, Bob in the EEInfo table.

I need a clue, please.

[June7]

Is Sheet1 an import of Excel worksheet or is it a link to a worksheet?

Why does this table have repetitive names? In a relational database the table would have only the Rep ID. There would be a Reps table with unique records for the Reps. Change the name in the Rep table and this change will be reflected in queries that join the related tables.