Change query, updated column based on cel value

[petertenthije]

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.

 

[xenou]

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

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]

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

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!