Use One Update Query to Update 3 Fields?

[joel009]

I am trying to run one update query to update 3 fields in 2 tables based on the users input.

Here is the SQL for the query:
UPDATE [T_User/Requestor] INNER JOIN T_InventoryBilling ON [T_User/Requestor].U_UserID = T_InventoryBilling.IB_UserCode SET T_InventoryBilling.IB_DepartmentNumber = UCase([T_User/Requestor].[U_DeptNumber]), T_InventoryBilling.IB_Physical_Location = UCase([T_User/Requestor].[U_DeptNumber]), [T_User/Requestor].U_DeptNumber = UCase([Enter New Dept No])
WHERE ((([T_User/Requestor].U_UserID)=[Input ID]));

The problem is the first record does not update to the new department number. I have tried putting the input for the new department first and it makes no difference. There are 5 records affected in the query and the first record always stays the original department number. How do I reference the input box for all 3 fields being updated without having the user enter the data 3 times?
Any Ideas???

 

[PHV]

You may try this:
UPDATE [T_User/Requestor] As U INNER JOIN T_InventoryBilling As I ON U.U_UserID = I.IB_UserCode
SET I.IB_DepartmentNumber = UCase([Enter New Dept No])
, I.IB_Physical_Location = UCase([Enter New Dept No])
, U.U_DeptNumber = UCase([Enter New Dept No])
WHERE U.U_UserID = [Input ID];


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

 

[joel009]

Thanks PH, I didn't know the input box would only come up once for multiple entries if the syntax was the same. This worked great!!!

Joel