Results 1 to 10 of 10
  1. #1
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14

    Using Concatenate to Update a Column Without Duplication

    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.

    Code:
    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)
    Thank you for your help in advance

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    'case when' is Tsql used in sql server - so are you writing this code in sql server or access?

    your data is not normalised which is why you are having such a problem. Normalise the data first

  3. #3
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14
    This is SQL server. This actually bring me to another question, when handling these type of changes, would it be better to do it in the BE or the FE?

    But is there any other way around beside from normalization?

    If normalization is required then this could be a stopping point for me then as I do not have enough rights in the database that I have access to. I've only been given enough to modify the tables pertaining to the access app that I am maintaining within that database.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    FE or BE? - depends on what you are going to do with it. BE will usually be faster

    you can normalise data by using a union query

    SELECT recID, Col1 FROM tblDockets
    UNION SELECT recID, Col2 FROM tblDockets
    UNION SELECT recID, Col3 FROM tblDockets


    UNION SELECT will remove duplicates, UNION SELECT ALL retains them

    but your displayed data does not have a recID so not sure this is a possible way for you to go

    but if it is the next phase would be to use a cross tab (or the tsql equivalent) or if this was in access the concatrelated function but does depend on what you are using the new column for. It may be that the union query is all you need

    If this is a one off and depending on what you want then look at using the nullif function - modify your original query to something like

    set newcol=col1 & nullif("," & col2,"," & col1) & nullif("," & col3,"," & col2) & ","


    this does assume that same values are adjacent to each other.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can you build an update query based on a union query? I've never tried. I would be interested to know the outcome. I think I would be more inclined to do this with code and plan for each of the 5 contingencies (below) in case something changes in the future it would also allow for duplicate values in all three fields (1st Row) or having a non duplicate between values (3rd Row)


    Field1 Field2 Field3
    A A A
    A A B
    A B A
    B A A
    A B C

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Can you build an update query based on a union query? I've never tried.
    good point. I've not tried either.

    your scenario also needs to include nulls or zls

    All depends what the OP is doing - why create the field and populate it? why not just calculate it when required? why not use the union query to create a normalised table and work from there? - OK last one OP needs permission to create a table so the union query can append

  7. #7
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14
    Thanks all for the suggestions so far. I will try and get back, this will take me sometimes.

    Unfortunately I doubt I will be able to get that permission to create as the database contains other tables beside from the ones I'm maintaining, and as results, the admin refused to grant me that right.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by 123noob View Post
    <snip> I am trying to merge 3 columns' values into a new column and so far everything is good.
    I'm curious as to WHY merge the 3 columns? What is the purpose - to print a report? Is this for long term storage?
    How much data will have to be processed (Number of records)?

    Maybe create a view in SQL Server with only the fields required, then use VBA to process the records, saving the data in an Access table (the FE)..??

  9. #9
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14
    These three fields are for assign tasks to that and have no relation to anything else other than itself and other field within the table for tracking purposes.

    Task1:Col1
    Task2:Col2
    Task3:Col3

    So Col# columns are use for assign user to the task but have no direct relation, just only on the FE that they are viewed as together (hopefully I'm making it clear enough - please excuse my English).

    So now the request is that they no longer need to use total of 6 fields (3:3) and make it into 1:1 only. So the new columns would be used as a multi value fields and for new records, not a problem. The only problem that I am getting into is what is listed above in order to combined existing user assigned to multiple tasks to show up only one in the new field vs multiple times.

    Your suggestion is good, since I cannot create in BE, I would have to do it on the FE which I can through access, then update the BE table from there. The process may take longer for the load time though.

    Thanks Steve!

  10. #10
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    you can easily do this with a SQL query:

    Code:
    update  tblTest set tstColNew =
                 
    case when LEN(coalesce(tstCol1,'')) > 0 then coalesce(tstCol1,'')+'; ' else '' end
                
    +case when coalesce(tstCol2,'') <> coalesce (tstCol1,'') and len(coalesce(tstCol2,'')) > 0 then coalesce(tstCol2,'') + '; ' else '' end
                
    +case when coalesce(tstCol3,'') <> coalesce(tstCol1,'')   and coalesce(tstCol3,'') <> coalesce(tstCol2,'')    and len(coalesce(tstCol2,'')) >  0  then coalesce(tstCol3,'') else '' end
       
                
    

Please reply to this thread with any new information or opinions.

Similar Threads

  1. concatenate text column by grouping?
    By aero in forum Queries
    Replies: 2
    Last Post: 07-27-2016, 03:11 PM
  2. Replies: 3
    Last Post: 05-28-2015, 05:14 PM
  3. Replies: 3
    Last Post: 09-13-2012, 11:14 AM
  4. Replies: 3
    Last Post: 12-07-2011, 03:02 PM
  5. Replies: 3
    Last Post: 10-16-2009, 09:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums