Change query, updated column based on cel value

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
206
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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,666
Office Version
2013
Platform
Windows
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.
 

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
206
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,101,768
Messages
5,482,801
Members
407,363
Latest member
lauren1932

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top