Page 3 of 3 FirstFirst 123
Results 31 to 32 of 32
  1. #31
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,


    I have been shovelling and skyping.

    I have looked at removing the duplicates, now that there is an option to sort output from ConcatRelated.
    I spent some time proving it shouldn't be done inside ConcatRelated. Then I said, why not just process the output with a user defined function.

    So here is the function:

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: removeDuplicates
    ' Purpose: Remove duplicate value from a separator based- Ordered  string
    ' Procedure Kind: Function
    ' Procedure Access: Public
    ' Parameter StrList (String): List of values to be parsed to remove duplicate values
    ' Return Type: String
    ' Author: Jack
    ' Date: 17-Jan-22
    '
    'INFO: This was set up to remove duplcates from an Ordered result from ConCatRelated.
    'NOTE: The incoming string should already be ordered, but may have duplicates
    ' ----------------------------------------------------------------
    Function removeDuplicates(StrList As String) As String
    10        On Error GoTo removeDuplicates_Error
              Dim good As String             'this is the string to be returned
              Dim var As Variant             'variable to contain an array after split of strLIst
              Dim el As Variant              ' an iterator for reading individual elements of arraay
              Dim unkn As Variant            'a starting value for comparison with el
              Dim strSep As String           'separator for output ", "
              strSep = ", "
    20        unkn = "Z"
             ' StrList = "1 ,1 ,1,2  ,2,3 ,4 ,4 ,6"   'test setup
    30        StrList = Replace(StrList, " ", "")
    40        var = Split(StrList, ",")
    50        For Each el In var
    60            If el <> unkn Then
    70                good = good & el & strSep  'uses ", " separator; this could be an optional char
    80                unkn = el
    90            Else
                      'skip a duplicate
    100           End If
    110       Next
              removeDuplicates = Left(good, Len(good) - Len(strSep))
    120       On Error GoTo 0
    removeDuplicates_Exit:
    130       Exit Function
    
    removeDuplicates_Error:
    
    140       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure removeDuplicates, line " & Erl & "."
    150       GoTo removeDuplicates_Exit
    End Function
    And here is how you call it.

    SELECT Query1.foundword
    , removeDuplicates(ConcatRelated("[Jobtitleid]","[01_tbl_WordDictionary_NoDups]","[foundword] ='" & [foundword] & "'","jobtitleId")) AS ConCaTEst
    FROM Query1
    WHERE (((Query1.foundword) Not Like "*'*")
    And here is partial result.

    Click image for larger version. 

Name:	TomRemoveDupsIfConcatRelatedSorted.PNG 
Views:	16 
Size:	23.4 KB 
ID:	47090
    Last edited by orange; 01-17-2022 at 07:56 PM. Reason: edited to remove trailing separator

  2. #32
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack:

    I hope the amount of accumulated snow is within reason. I live in Southeast Virginia (Norfolk area) and we didn't get any accumulation this time around. Not trying to rub it in... 'just saying we are fortunate to have been spared during this latest storm.

    I looked at the new query which removes the duplicates. This is FANTASTIC!!! I love the idea as to how you (so quickly) developed a custom function to strip out the dupes.

    Btw, I'm not sure if you had a chance to see post #30. There, I indicated this this new baseline (i.e,, identification of the ID) truly meets this current requirement. Now, having a cleaner version of this (w/o dups), I'm even better prepared to move into the next phase of this project.

    So, little more than an hour ago, I posted a new thread that outlines the steps for the final cleanup process. As previously touched on, the ultimate goal is to come up with an automated routine which (hopefully) would be able to identify, e.g., "Analyzed" or "Analyzing" and change it to "Analyze". In the end, this would change all verbs written in *past tense* or *present tense continuous* to *simple present tense*.

    The URL for this new post is as follows: https://www.accessforums.net/showthr...108#post489108

    I'd love to see if you may be able to offer any ideas on that final step.

    In the meanwhile, please allow me to attach the most current version I have (with the latest custom function as well as the updated naming convention for tables, queries, etc.). That said, the removal of the dups can be found in query "05_qry_FoundWordInJobTitleIDQ_Output_WithoutRepea ts" (also, I kept query "04_qry_FoundWordInJobTitleIDQ_Output_WithRepe ats" for comparison purposes).

    Finally, based on the new post, I provided the forum readers a DB that contained only 10 records. You've working with the table that contained the 40 records. Either way, this latest version contains both source tables. Naturally, if you wanted to run it on the 10 records, simple change the table name from "00_tbl_Source_Data_10_Records" to "00_tbl_Source_Data" while giving the one w/ the 40 records another temporary table name. But you know that... preaching to the choir here.

    Again, thank you for all the dedicated work in this thread. I'm super excited.

    Looking forward to the next step in the new post. Hope to see you there...

    Cheers,
    Tom
    Attached Files Attached Files

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-29-2018, 10:26 AM
  2. Replies: 6
    Last Post: 06-06-2018, 06:56 PM
  3. Replies: 14
    Last Post: 01-08-2016, 07:09 PM
  4. Move a single record to an archive table
    By 10 Gauge in forum Forms
    Replies: 7
    Last Post: 02-14-2011, 06:50 AM
  5. Replies: 7
    Last Post: 11-13-2010, 08:08 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