Change query, updated column based on cel value

petertenthije

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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,670
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
207
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,102,381
Messages
5,486,532
Members
407,550
Latest member
LucasBordure

This Week's Hot Topics

Top