Help inserting values from one table into another

[sweetp]

Hi everybody. I'm excited that someone might be able to help me, since I'm a newbie here.

I have a table EMPLOYEES. It has a column EMPLOYEES.SUPERVISOR (which is blank right now). Another table has the list of SUPERVISORS. I would like to put each appropriate supervisor's last name (SUPERVISORS.LASTNAME) in EMPLOYEES.SUPERVISOR. Both tables have a common column (.ORG). The statement I entered in the Row Source for EMPLOYEES.SUPERVISOR is:

SELECT SUPERVISORS.LASTNAME FROM SUPERVISORS
WHERE SUPERVISORS.ORG=EMPLOYEES.ORG

But it only gives me a combo/drop down box - instead of inserting the name automatically, and the drop down box only has 1 name in it -- there are 6 supervisors.

What am I doing wrong????

 

[dalchri]

What you are after is an update query:
[tt]
UPDATE Employees LEFT JOIN Supervisors
ON Employees.Org=Supervisors.Org
SET Employees.Supervisor=Supervisors.LastName
[/tt]
Don't enter this query as the row source, this query is by itself the entire query. I replaced your WHERE clase with a LEFT JOIN. This way, any employees that don't have a matching supervisor will get a Null value in their Employees.Supervisor column.

 

[sweetp]

Where do I put this code if I can't put it in row source for the EMPLOYEES.SUPERVISOR column? The table EMPLOYEES now has all the employee data entered statically (via a form), so I only have the EMPLOYEES.SUPERVISOR column blank which needs to be automatically populated via the SQL query.

Thanks for all your help. This is great.

 

[khood]

Open up a new query & choose design mode. Choose View-SQL. Copy & paste the SQL code that dalchri has given you. You can run the query immediately by hitting the ! on the toolbar.

K.Hood

 

[sweetp]

It worked! Thank you both so much!