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?

 

[welshgasman]

Try a simple join?

 

[BarbaraT]

Try a simple join?

I'm not sure I understand what you are suggesting. If I join the two tables using the Employee ID on the link, then when I ask for the employee name in the query I'll get the name of the original employee, not the reassigned one. So, for client Green, the query will show the employee name as Suzy (the name of the employee assigned the ID in the client table), not the newly assigned employee, Fred.

 

[welshgasman]

I believe you would need to create query 1 with your calculated field.?
Then join that to your employee table in query 2.

You want to create the join with the calculated field and the id of the employee, not the original empID.

You might be able to do it in one query. Try it, you have nothing to lose. I have not done it myself, but worth a try?

 

[BarbaraT]

I see how I can do it with two queries - and the real one isn't much more complicated than the sample I gave. It's putting it into a single query I'm hoping to do and can't figure out how to do the lookup. There's a "lookup" tab in the property sheet but I'm not sure how to use it.