UPDATE with multiple JOINS 1

[gregz0012]

I have no idea how to do this so maybe some kind sole could help me out. I have been reading documentation and trying to get this to work for hours now

Here's what I have.

UPDATE
Users
SET
Users.FirstName = 'Joe', Users.LastName = 'Bloggs', Users.Manager = Managers.Manager,
Users.Location = Location.LocationID, Users.Department = Department.DepartmentID
FROM
Users INNER JOIN (Users INNER JOIN (Users INNER JOIN Managers
ON Users.Manager = Managers.Manager) Location ON Users.Location = Location.LocatioID)
Department ON Users.Department = Department.DepartmentID
WHERE
Managers.FirstName + ' ' + Managers.LastName = 'Zoe Brummit' AND
Location.Name = 'Boston' AND
Department.Name = 'Servicing' AND
Users.Personnel = 66

In english I need to update the users firstname, lastname, manager, location, and department where the values for the latter three are pulled off the corresponding tables by their names. Doesn't sound that complex but when you don't know SQL it is.

Thanks in advance, this one has me well and truely stumped. I'm not even sure if to / how to nest the joins.

 

[PHV]

UPDATE Users
SET FirstName = 'Joe', LastName = 'Bloggs'
, Manager = (SELECT Manager FROM Managers WHERE FirstName & ' ' & LastName = 'Zoe Brummit')
, Location = (SELECT LocationID FROM Location WHERE Name = 'Boston')
, Department = (SELECT DepartmentID FROM Department WHERE Name = 'Servicing')
WHERE Personnel = 66

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

 

[gregz0012]

That looks much simpler

I get this however "Operation must use an updateable query." Though I think that is related to my database, yes?

 

[PHV]

You may consider the DLookUp function.

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

 

[gregz0012]

DLookUp works great within Access however not by using ODBC Microsoft Access Driver / Jet. Any way around this issue or will I need to perform the select queries first and then put them into the update query?