Results 1 to 3 of 3

Thread: Change query, updated column based on cel value

  1. #1
    Board Regular
    Join Date
    Sep 2012
    Posts
    127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change query, updated column based on cel value

    I have "inherited" a database to maintain.

    In it is TABLE1 with a unique staff number, followed by several columns with the qualifications each staff member can have.

    In TABLE2 I have feedback from trainers. They fill this in to let me know someone has a new qualification. There is one field (TYPETRAINING) in which the teamleader enters the qualification. This is secured with data validation. If someone gets more then one new qualification, then two (or more) rows are added to TABLE2.

    is there a way for me to create a change/update query, where the value of TABLE2!TYPETRAINING determines which column in TABLE1 gets updated?


    p.s.
    I realise that ideally TABLE1 should be reformatted to have one column with qualification description and one column with untrained/training/trained. But since there are a lot of tools linked to this database, this won't be possible.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,474
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Change query, updated column based on cel value

    You could run four (or more queries) with the understanding that only one will really have an effect:

    Code:
    update Table1 set Training1 = 'x' where [Table2]![TypeTraining] = 'Training1' and StaffID = 12345
    update Table1 set Training2 = 'x' where [Table2]![TypeTraining] = 'Training2' and StaffID = 12345
    update Table1 set Training3 = 'x' where [Table2]![TypeTraining] = 'Training3' and StaffID = 12345
    update Table1 set Training4 = 'x' where [Table2]![TypeTraining] = 'Training4' and StaffID = 12345
    Something similar could be done with IIF() statements to create a single mega-query that is literally one query if you really so desire - I find such queries with a lot of nested IIFs too ugly.

    Also, if you use some code logic you can pretty much do anything - this wouldn't take too much in the way of If-Then logic to fit the task, either by running one of several queries depending on the IF condition, or you could even build the sql dynamically if you wanted to have fewer lines of code.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Sep 2012
    Posts
    127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change query, updated column based on cel value

    Quote Originally Posted by xenou View Post
    You could run four (or more queries) with the understanding that only one will really have an effect:

    Code:
    update Table1 set Training1 = 'x' where [Table2]![TypeTraining] = 'Training1' and StaffID = 12345
    update Table1 set Training2 = 'x' where [Table2]![TypeTraining] = 'Training2' and StaffID = 12345
    update Table1 set Training3 = 'x' where [Table2]![TypeTraining] = 'Training3' and StaffID = 12345
    update Table1 set Training4 = 'x' where [Table2]![TypeTraining] = 'Training4' and StaffID = 12345
    Something similar could be done with IIF() statements to create a single mega-query that is literally one query if you really so desire - I find such queries with a lot of nested IIFs too ugly.

    Also, if you use some code logic you can pretty much do anything - this wouldn't take too much in the way of If-Then logic to fit the task, either by running one of several queries depending on the IF condition, or you could even build the sql dynamically if you wanted to have fewer lines of code.
    Apologies for my late feedback, but I just wanted to thank you because this works great!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •