Results 1 to 2 of 2
  1. #1
    SealM is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    40

    UPDATE query to update a field from within the same Table

    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

  2. #2
    SealM is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    40
    Solved my own problem, after more research and digging on the internet with this eloquent query:

    UPDATE data_users as A
    INNER JOIN data_users as B
    ON A.supervisor_FK = B.user_PK
    SET A.supervisor = B.user;

    Thanks for everyone who was ABOUT to help me tho! 'Til next time

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-30-2012, 07:59 AM
  2. Replies: 1
    Last Post: 02-08-2012, 04:50 PM
  3. Replies: 1
    Last Post: 08-31-2011, 04:03 PM
  4. Update field in table after update on a subform
    By jpkeller55 in forum Queries
    Replies: 3
    Last Post: 09-30-2010, 08:02 PM
  5. Update Field list in Table with Query
    By Scorpio11 in forum Queries
    Replies: 3
    Last Post: 07-16-2010, 01:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums