Hi again everyone! This is a really easy question (I think), but I'm stumped on it. Before I begin, let me assure you, I KNOW I'm doing a very bad thing here, by having reduntant data in a table, but I have too many other updates and queries that rely on this table, and I don't have time to go back and change them. I have had to add an additional FK field to a table to resolve an update issue, and now I have to put the 'old' field back in it, but I need to update the field with a query. This probably doesn't make sense, but rest assured I'm well aware I am not following good practice database design with this question:
I have a table with the following fields (among others):
TABLE NAME:
data_users
Fields:
user_PK (autonumber)
user (text)
supervisor_FK (number)
supervisor (text)
Every user has a supervisor. the "supervisor_FK" is the "user_PK" that is the person's supervisor. So, all users are in the table, and some users in the table can be a supervisor, so the same table records the supervisor from the list of users. (I hope that's not too confusing).
Now, everything works properly in the table, and the forms work as they were made to show a drop-down list of users that can be selected for each users. This is recorded as the supervisor_FK for each usuer.
The query I need to make is an UPDATE query. It needs to update the "supervisor" field with the NAME of the supervisor, which would come from looking up the matching "supervisor_FK" in the "user_PK" field, and copying the "user" into the "supervisor" field.
I know the syntax is wrong for this query, but the only examples I have for doing this come from using an INNER JOIN command because this would normally be done with TWO tables when dealing with PKs and FKs.
Here's what I have:
UPDATE data_users WHERE data_users.supervisor_FK=data_users.user_PK SET data_users.supervisor = [data_users]![user];
Can anyone tell me what the right way to do this is, to get the "supervisor" field to update correctly?
Thanks in advance,
Mike