Have you ever tried querying for a person’s profile record from a database to modify their information? Let’s say if the person has left the company and you would like to change the person’s profile status from “Active” to “Deleted”. It may sound like a simple task but what if you’re from a large organization and this is the first time your company has implemented this rule where an administrator needs to update the status of their staff’s profile from “Active” to “Deleted” when they leave the company. Your boss has tasked you to update all the past records of ex-staffs who have left the company but you are only provided with a long list of staff’s code. Would you have to do a search on each user individually to update each profile one by one?
Today I will be sharing on how we can make our life easier by creating a single query to search for the affected records.
Firstly, we will need to put the list of staff codes into an excel file and then place it all in column B or in the Middle of any 2 columns.
Next, we can add in the name of the value we are searching for, close the query and adding an “OR” to the end of the query to state that theirs is more condition to be met.
After that, you can highlight the first two rows at column A and then place your cursor at the bottom right of the row, your cursor will then turn into a “+”. Left click and drag your cursor down vertically till row 9 to auto populate the cells below with the same value. Do the same for column C.
After auto populating column A and C, we can use the concatenate function in column F to join the values in column A, B and C together for row 1.
Do the same for row 2 by creating the formula “ =(A2&B2&C2)”.
After that, highlight row 1 and 2 for column F, place your cursor on the bottom right then drag down vertically. It should auto populate the pattern for the following cells.
Now that we have the query there is a final step to transpose all the values in column F from vertical to horizontal.
Highlight F1-F9, then copy what you have highlighted. On any empty cell, right click and select “Paste Special”. Then select “Values” under the Paste section and check “Transpose” at the bottom. Click OK after that.
You’ll have a line generated, immediately copy (Ctrl + C) it without clicking anything and then paste it in a notepad. You will finally have a string of long query. You can modify all the records using 1 SQL statement.
Hope this will be useful to you as it is for me, by saving me the hassle of modifying the records one by one. Thank you.