Update Query using name matching algorithm

[shaunk]

I have a table called Issues containing the Employee ID of the User(Provider) reporting the problem.
The Issues table is in a relationship with a table called User_Name, linked on Employee_ID.
User_Name is a linked table to an external database table.
I have just discovered that the external table is not updated with new Employees. Doh !
To correct this, I have created another User Name table called Provider_Name. This is linked to an external table that is kept current. However, Provider_Name does not contain Employee ID, it contains Provider_ID. So I can't just link it to Issues on Employee_Id and solve the problem.
I have to create another field in Issues called Provider_ID, and populate it in an update query.
To do this, I have to match [User-Name.Employee_ID] with [Provider_Name.Provider_ID] based on a First Name/Last Name match. This is dodgy, given the possibility of two users with the same name, but looking at the data, I can get away with it at this stage.
My problem is, finally, how do I achieve this in one update query, or at all using Access.
I know how to do this in ASP using it's procedural capability, but this is a an Access learning challenge for me.
If your still with me, thanks for getting this far.

 

[PHV]

I know how to do this in ASP
How are you doing this ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[shaunk]

I haven't got an exact method, but it would be something along the lines of populating a two dimensional array or collection with Issue.Issue_ID and a correspondig Issue.Provider_Id that is given a value according to a name match on reporting User.
I would then use a Do..While or For...Next construct to update each Issue in turn.
The details are murky, but it would just be a case of working it out.