Access query lookup calculated field in table

[BarbaraT]

I want to have a query calculate a field (and ID) and then lookup that ID in a table to turn it into clear text.

Say I have two tables - one with Clients and the Employee Rep associated with them, and the 2nd with employee IDs and names. But, the workload is reorganizing, and so some clients will be assigned new employee representatives. I want to use a query to generate those new client/employee relationships.

The ID of the new Employee Rep is calculated in the query (changing B to A) and then I want to lookup the new ID in the employee ID table to get the new name. How do I do that lookup within a query?

 

[FormR]

Hi, bit late to the party, but you couldn't you just add the tables to the query without a join and then put your calculated ID in the criteria of the Employee ID field. Something like..

 

[Micron]

Nice solution. However, I think I'd add Not Like "*A" so that you don't get records with 123A | 123A | Fred.
I took it that what was wanted was to change the name associated with the client, which is why I said to use update query. Perhaps that was not what was wanted.