Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    VBA routine to update matching words (via AutoID) with new a new/different word

    Experts:



    In a previous post, forum experts @June7 and @Orange (Jack) provided some superb assistance with developing a VBA routine (plus additional queries) which ultimately
    allowed me to extract existing "words" (from a string) and then cross-reference their source (autonumber) ID. Please see post https://www.accessforums.net/showthread.php?t=85162 for additional details.

    Given the length of the other thread, there's no need to fully re-hash the previous requirements. However, please allow me to briefly summarize the process (attached DB) as it is somewhat important for the new requirement.
    - Table [00_tbl_Source_Data] includes some job descriptions. For demo purposes only, these string values are extremely short/abbreviated.
    - The job descriptions include "action words" either at the beginning or in the middle of the sentence.
    - The actions words are written in different tenses, e.g., *past tense* OR *present perfect/continuous*. Examples are: "analyzed", "analyze", or "analyzing", etc.
    - Ultimately, my goal is to change instances of a) "analyzed" OR "analyzing" into b) "analyze".

    An illustration is provided in the attached JPG, where:
    - Step 1 is currently an automated process (the DB attached). That is, words from each string are extracted and query "04_qry_FoundWordInJobTitleIDQ_Output" cross-references the source JobTitleID.
    - Step 2 is currently a manual process. Additional details are explained below ("Evaluation of Words").
    - Step 3 is what I need some help with (e.g., development of a new VBA routine).

    Current VBA Procedure:
    - Again, the other post provides a great level of detail as to how values are derived in "04_qry...". I won't rehash any specifics here.
    - However, I will say that -- if you wanted to add a few additional strings/job descriptions in table [00_tbl_Source_Data], then simply click on the form's command button and view the findings in query "04_qry..."

    Ok, now let's go back to the manual process "Evaluation of Words":
    - Table [99_tbl_WordDictionary_Word_Evaluation] is essentially a copy of auto-generated table "02_tbl_WordDictionary_NoDups".
    - The only difference is the addition of 4 fields:
    1. Verb (Boolean) -- TRUE value indicates that word is considered a *VERB*
    2. Verb_NonVerb (Boolean) -- TRUE value indicates that word is considered both a *VERB* as well as *non-verb* (e.g., word "processes" can be either verb or noun (plural)).
    3. NonVerb (Boolean) -- TRUE value indicates that word is NOT a verb (i.e., anything else such as noun, adjective, etc.)
    4. UpdateTo (short text) -- Identifies the value to which the found word should be updated to in table [00_tbl_Source_Data].

    Envisioned Process (this is where I need some help):
    - Based on table [99_tbl_WordDictionary_Word_Evaluation], the VBA routine should scan through [00_tbl_Source_Data].[Skill] and update every matching word.
    - For example, in record #3, the action word is "Analyzing". Given its tense = *present perfect/continuous*, I want the VBA routine to automatically update the word to "Analyze" (or "analyze").

    Given that some actions words are also nouns, the criteria for the update routine should be based on the following:

    Update words, WHERE:
    ** [tbl_WordDictionary_Verbs].[Verb] = TRUE AND
    ** [tbl_WordDictionary_Verbs].[Verb_NonVerb] = FALSE AND
    ** [tbl_WordDictionary_Verbs].[NonVerb] = FALSE ...

    or even easier, only update words where field [UpdateTo] has a value. If [UpdateTo] = Null, do NOT update the word in [00_tbl_Source_Data].

    I fully understand that this may be a challenging VBA routine. I am hopeful though that someone may have done some related work (e.g., matching/updating words via the autonumber).

    As I have thousands of job descriptions (with much longer sentences than those example ones I listed), an automated VBA routine work be greatly appreciated.

    Cheers,
    Tom
    Attached Thumbnails Attached Thumbnails PictorialReview.jpg  
    Attached Files Attached Files

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    I think you are over complicating things; why update the source table when updating the one with dups after processing via a simple update query (see attached) will achieve the same thing much faster and efficient, and more important without interfering with the source data.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Hello Vlad -- great to "see" you again. I hope all is well w/ you & family in cold BC.

    Yes, I have the tendency to sometimes overcomplicate things... so, I'm always glad to hear other opinions which remind me to keep it simple (KIS).

    I looked at the file you included and ran the new Update query. Per the attached screenshot, it's great to see that the respective verbs have been updated. However, as included in the illustration, I don't see (just yet) where/how the entire sentence is updated.

    Did I miss something in the attachment? Or would that be another query that strings those elements (i.e., updated verbs + original nouns/adjectives, etc.) back together? I apologize if I missed anything obvious.

    Again, thank you for the prompt response... I always feel in good hands when either you (or Jack) respond to my posts.
    Attached Thumbnails Attached Thumbnails FollowUp.jpg  

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think what you need now is another column in that WordDictionary table that specifies what the found word should be updated to. Then run a procedure that loops through dictionary words and executes UPDATE (or Find/Replace) on the sentences table. So, this update should not be run on the dictionary table.

    As you pointed out in other thread, knowing when "documents" should be considered a verb and changed to "document" is a complication. What if there is: "Documents receipt of official documents."? Preserving upper case when appropriate is another complication. What if the same word is used more than once in same sentence and one is capitalized as first word? Probably unlikely scenarios but not impossible. Unless you have a fully functional AI that can understand these distinctions, you will have to make decisions.

    No matter how carefully you structure this dictionary table, expect some errors.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    Nice to "see" you too . BC warmed up a bit now, we had a unusually cold snap during the holidays (with some devastating rainy days in November), it is more seasonal now.

    I was under the impression that the whole exercise was to get you the list of words, not to update the source table. If that's what you're after please review this updated version, there are two new queries but the update one does the job (the other one might not be needed, it is needed to keep the proper case).

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 -- thank you for the additional thoughts.

    I totally agree w/ both...
    1. Table "99_tbl_WordDictionary..." already contains that field referencing the value to which the verb should be updated to. See attached JPG.
    2. And, yes, please see record #10 (depicted in the original posted JPG) which includes a similar sentence: "Document documents" (with structure verb/noun). However, only the verb "Document" meets in criteria to be updated.

    Finally, I fully expect that some erroneous updates may be included. I am hopeful, however, that the volume of updating introduced errors will be less time-consuming than update the 1000s of records manually w/o any assistance of automated scanning/cleanup.

    Cheers,
    Tom

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

    Thank you for taking time and offering some additional thoughts/solutions to the challenge. I reviewed your additional 3 queries. The output (i.e., updated [00_tbl_Source_Data] looks extremely promising!!

    BREAK....

    - I had written down additional questions (based on the **3** new queries), but then realized that query "qryUpdateFoundWord_in_WordDictWithDups" **appears** to create a conflict (or undo some work).
    - Thus, below questions are now based on **2** new queries:

    1. qryNeedLowerCase
    2. qryUpdateSourceData

    --------------------------------------

    Allow me to first say that the review of the data set is based on the 10 basic sentences. I will later change the source data with additional (and longer sentences). First things first though, I would like to recap the existing process
    and ask questions (based on initial findings) w/ this much smaller data set.

    Question #1:
    - Assumption: A record is only updated if [99_tbl_WordDictionary_Word_Evaluation].[UpdateTo] is NOT null.
    - Question: If so, would it be okay to either a) delete the 3 Boolean fields or b) skip marking/checking the 3 Boolean fields? Either way, this could be a significant "time saver" during the manual evaluation of words.

    Question #2:
    - Assumption: Table [99_tbl_WordDictionary_Word_Evaluation] has been reviewed AND required [UpdateTo] words have been added (where needed).
    - Question: Is there anything I need to be aware of WRT query "qryNeedLowerCase"? Is it interpreting lower/upper case based on a) original source sentence or b) lower/upper case in field [UpdateTo]?

    Question #3:
    - Assumption: As indicated in opening sentence of post #7, it appeared that running query "qryUpdateFoundWord_in_WordDictWithDups" first, followed by executing query "qryUpdateSourceData" seemed to not update values (or something else).
    - Question: What is the purpose of "qryUpdateFoundWord_in_WordDictWithDups"? Is it needed or can/should it be deleted?

    Recap of steps:
    - Execute sub/function (via form).
    - Manually evaluate words in table [99_tbl_WordDictionary_Word_Evaluation].
    - Where necessary, add [UpdateTo] words in table [99_tbl_....]. Based on question #2, do I need to ensure the correct upper/lower cases here?
    - Execute **only** query "qryUpdateSourceData"?

    Again, thank you, Vlad, for offering a great solution to this process. As indicated, I will conduct additional analysis with a different data set later on. For now, having a better understanding of the existing process would better prepare me to run a larger data set.

    Cheers,
    Tom

    P.S. I attached 2 screen shots (scenario #1 and #2). They illustrate the dilemma when dealing with words that could be considered either a "verb" OR a "noun". So, in this case, the base "document"(s) may be updated erroneously. As stated though, contextual sentence analysis should mitigate those (potentially unlikely) situations.
    Attached Thumbnails Attached Thumbnails Scenario_1.jpg   Scenario_2.jpg  

  8. #8
    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
    Morning all!

    Some interesting stuff.
    Tom, can I ask what the ultimate goal is, and maybe more specifically the major steps/milestones between here and there.
    It's been years since I did some work related to text processing/searching/indexing etc.
    I did a quick search on google looking for some of the "terms" I could recall --tokens, stemming....
    I found this youtube that might offer some direction or focus.

    I have no experience with the product mentioned. My only thought here is that it contains some ideas and processes that you may find useful.

    The attached draft query sql may be useful. It is meant to work with your latest foundwords with No Dups.
    Attached Files Attached Files
    Last edited by orange; 01-18-2022 at 08:47 AM. Reason: additional info

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- indeed, I think this has been an interesting "challenge". Naturally, w/o the help from forum experts (i.e., you, Vlad, June7) I wouldn't be where I am right now (WRT to current solution/status).

    To answer your question about "what's the ultimate goal?", allow me to summarize in bullet format:
    - This is part of an HR-related initiative (I don't want to call it a project since we're merely helping out the HR team).
    - HR team has a system (for job requirements)... naturally, their system is rather extensive -- and I won't be able to cover those details in this open forum here. However, I can say that one of their fields stores "job descriptions" (JDs).
    - I don't know as to why they were written in different tenses (may have been simply due to different users having different writing style preferences). The latter is not the point though... what we were asked is to now standardize the language into present tense.

    Ultimately, that's really it. Going forward though (independent from this initiative), this semi-automated mechanism can be applied to other programs. Essentially, it's the evaluation of the words/language that may be time-consuming. However, one a word dictionary has been established, it's merely a matter of maintaining it (i.e., adding new words). Then, I guess, any scenario -- whether job descriptions -- or others that include actions verbs can be updated rather easily.

    Hope this helps.

    Cheers,
    Tom

    P.S. I'll have a look at the RootsVerbNoun ASCII file and do little more research on Youtube (as you recommended). Thanks for that tip!

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Tom,

    As you mention the new process (update source data) is based on only on two queries, the qryUpdateFoundWord_in_WordDictWithDups was left there in error as it was the original suggested solution when I thought the target of this exercise was the output list of words, not the original source. You can safely remove it and\or ignore it.

    Answer 1: Right now the only criteria used is UpdateTo Is Not Null, so you can skip updating the boolean fields for now. You could consider building a form to maintain this table and have some code in the AfterUpdate event of the UpdateTo (when you add one) to check the suffix of the FoundWord and check the Verb if ends up in "ed" or "ing"

    Answer 2: the query determines the need for lower case based on the original word, not the UpdateTo entry

    Answer 3: you can delete the query

    I agree that the issue with verb vs non verb cannot be solved 100 % without human intervention. For example we could build some logic to determine that in "Documents documents" the first upper case occurrence is the action verb and the second is the noun and update accordingly, but that will fail in "Analyzes business needs and documents documents".

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- perfect!! Thank you for clarifying the questions / validating assumptions.

    WRT to the verb/noun scenario, I'm 110% confident that a JD will never include "Document documents" or something like it. That said, your 2nd example, is much more likely. In fact, a very probably statement could be "Document all requirements; meet with customer to discuss XYZ; maintain version-control of project-related documents." However, off the top of my head, the verb/noun scenarios -- within the same sentence are -- are still rare.

    However, more importantly, the next case is definitely occurring:
    JD #1: "Documents all requirements; meet with customer to discuss XYZ." ----> this one will need to be changed to "Document all reqs..." (which is what we want).
    JD #99: "Maintain version-control of project-related documents." ----> and by addressing JD#1, I **believe** JD #99 will be updated to ".... project-related document." (which is incorrect).

    So, in this case, base "document" is a verb in the 1st scenario, but a noun in the 2nd scenario. So, I'm not 100% certain how to handle those just yet.

    Vlad -- again, thanks for the additional guidance... it's very much appreciated.

    Tom

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad, Jack -- amongst other work-related stuff, the evaluation of the "verbs" takes a bit longer than anticipated.

    Although I believe I have a solid method forward, I'd like to keep this thread open a bit longer until I actually execute the "verb update" function of the 1000s of job descriptions.

    I'll keep you posted (hopefully I'll have a more complete word dictionary/baseline in the coming days).

  13. #13
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sure Tom!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad, Jack, et al.:

    I previously indicated I would keep you posted on my progress. Allow me to summarize *initial findings* (based on 40 records) in bullet format below:

    1. As indicated in previous response, my actual data set contains 1000s of job descriptions (JDs).
    2. When testing the procedure with 40 actual JDs, the word dictionary (distinct words) reached 275 words.
    3. Thus, instead of evaluating a word dictionary with thousands (or potentially tens of thousands), I executed the entire process based on only the 40 test records.
    4. The results far exceed my expectations. With the exception of a few erroneous updates (some of them due to my making a mistake), the process/function resulted in 99.61% accurate updates. That is, only 2 out 511 words (or 0.39%) were updated incorrectly. I personally think that these are astonishing POSITIVE results.

    I have illustrated the findings/results (incl. Before/After) in the attached slide deck. The PPT is NOT meant to be a "business" product; however, I thought using illustration -- combined with comments -- will help facilitate the sharing of this information.

    And, given the work that Vlad, Jack, June7 provided in this (as well as previous) thread, I feel that I "owe you" some specific information. So, again, the content is meant to be informal knowledge-sharing (in a somewhat formal format), where:
    - slide 1 and 2 reference the BEFORE/AFTER data
    - slide 3 shows all JDs in the current/updated format (incl. undesired effects or the 0.39% equivalent errors).
    - slide 4 summarizes "room for improvement"... and
    - slide 6 provides the procedures executing the routine. With the exception of the manual word evaluation (which can take several hours), the execution of the queries/sub, naturally, only takes a few seconds.

    If you have any thoughts on slide #4, I'd welcome ideas/recommendations.

    All that said, this process is great and will definitely help standardizing the required string values (JDs). Once I have accumulated a better way of addressing issue (slide 4), I will embark on the work of evaluating the 1000s of JDs. Urgh!

    Thanks for all the work you've helped me w/ in this thread.

    Cheers,
    Tom

    P.S. Attached zip contains both PPT and ACCDB
    Attached Files Attached Files

  15. #15
    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
    Looks good and seems to assist reducing work/effort.
    Did you look at the Union query SQL I sent?? It may handle more verbs etc, but your hit rate/success is great.
    You can ensure first word starts with Capital with a little code.
    Perhaps a larger test set would help focus/refine things??

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-29-2020, 12:10 AM
  2. Replies: 9
    Last Post: 07-21-2014, 06:40 PM
  3. Update table with autoID
    By kris9 in forum Access
    Replies: 9
    Last Post: 08-09-2013, 12:08 PM
  4. Word Automation VBA: Make few words in range bold
    By besuchanko in forum Programming
    Replies: 1
    Last Post: 04-01-2013, 10:12 PM
  5. Replies: 4
    Last Post: 12-02-2011, 11:20 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