Hi, I am new with Access/VBA/and SQL and hope that someone could help guide me.
I am trying to merge 3 columns' values into a new column and so far everything is good. However, is there a way to remove a duplicate value(s) if it already presented once in one of the columns being concatenated?
tblDockets
Col1---Col2---Col3---ColNEW
1-AB-----AB------C
2-AB-----AC----NULL
3-A-------B-------C
After concatenation and update the ColNEW, the ColNEW I have currently is:
ColNEW
1-AB, AB, C
2-AB, AC,
3-A, B, C
My goal is not to have the duplicated values showing up more than once in the new column... I've looked through the web and only found info on removing the duplicated rows and not the actual values within that row. Below is my goal:
ColNEW
1-AB, C,
2-AB, AC,
3-A, B, C
For my query, I used Case/When and this is as far as I can get right now.
Thank you for your help in advanceCode:Update [tblDockets] set [ColNEW] = (Case when [Col1] IS NULL or [Col1] = '' then '' + case when [Col2] IS NULL or [Col2] = '' then '' + case when [Col3] IS NULL or [Col3] = '' then ''else [Col3]end else [Col2] + ', ' + case when [Col3] IS NULL or [Col3] = '' then ''else [Col3]end end else [Col1] + ', ' + case when [Col2] IS NULL or [Col2] = '' then '' + case when [Col3] IS NULL or [Col3] = '' then ''else [Col3]end else [Col2] + ', ' + case when [Col3] IS NULL or [Col3] = '' then ''else [Col3]end end end)