Update query issue

[doomy304]

Hello,

I am very very new to Access, so apologies if I get terminology wrong or make silly mistakes - I'm trying to self-teach with the help of tutorial books and the internet.

I have a database, with an Users table (containing LastName, FirstName, and Manager fields) and a MgmtNames table. The managers have divided up the agents by last name - there are four managers, the first one gets everyone with last names A through C, the second gets D through L, etc. What I would like is for the Manager field in the Users table to auto-populate the correct manager name when I add a new record to the Users table. The Manager field is numerical and linked to the ID field in the MgmtNames table.



So I thought the best way to do this would be via update query. I cleared any existing data out of the Manager fields in advance of running the query. The first time I set it up, it previewed just fine, but when I tried to run it, it made no changes and gave me no error messages. It would not run/preview on any fields after that, UNLESS there is something in the Manager field already, and in that case the preview only shows me whatever is already in the Manager field. I even deleted the query and recreated it (in case all the fields that got "updated" the first time were now flagged as "query has already run on these fields" or something like that) and still nothing.

Here is the SQL code:

UPDATE MgmtNames INNER JOIN Users ON MgmtNames.ID = Users.Manager SET Users.Manager = Switch([LastName] Like "[A-C]*",1,[Lastname] Like "[D-L]*",2,[LastName] Like "[M-R]*",3,[LastName] Like "[S-Z]*",4);

What am I doing wrong? Thanks in advance for any help!

[June7]

Don't see need for the join. Switch function is new to me. Actually works! Try:
UPDATE Users SET Manager = Switch(Left([LastName],1) Like "[A-C]",1,Left([Lastname],1) Like "[D-L]",2,Left([LastName],1) Like "[M-R]",3,Left([LastName],1) Like "[S-Z]",4);

[doomy304]

Don't see need for the join. Switch function is new to me. Actually works! Try:
UPDATE Users SET Manager = Switch(Left([LastName],1) Like "[A-C]",1,Left([Lastname],1) Like "[D-L]",2,Left([LastName],1) Like "[M-R]",3,Left([LastName],1) Like "[S-Z]",4);

THAT DID IT! Thank you so much!!