Change query, updated column based on cel value

petertenthije

Active Member
Joined
Sep 25, 2012
Messages
326
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top