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

[Gicu]

Hi guys,



Here is an updated file with some changes. I added a module with some string manipulation functions that I found a long time ago on the web and use in my projects. Based on that I updated the "ed_ing" query to only flag the words that appear in first position in any of the Skill records. I also created a small functions in the same new module to capitalize the first letter of the UpdateSourceData output.

Cheers,
Vlad

[skydivetom]

Jack:

Per your question "Did you look at the Union query SQL I sent?? It may handle more verbs etc, but your hit rate/success is great." Short answer... yes, I did. However, given the positive success rate, I didn't implement the mods **yet**. However, once I start transitioning into the larger dataset (i.e., the 1000s of records I spoke of), I certainly will have a closer look. Anything to further identify / streamline the word dictionary will help.

//


Vlad:

Thank you for posting v4. I very much appreciate the additional help/support.

At this time, I'm not fully clear on the process for the new *ed_ing* query. For some reason, executing it results in "0 records update". I'm sure the answer is simply... I am probably overlooking some very basic. Ultimately, I've been staring at the WordDictionary table for quite some time; things are getting "blurry". Probably time for a break.

Anyhow, may I kindly ask for some clarification? Attached zip file includes 2 files:

1. PPT with illustration and comments. Slide 1 and 2 focus on the non-update of the new *ed_ing* query. Alternatively, slide 3 focus on a possible extension/improvement (assuming the new query updates records as expected).
2. Given the # of objects in the ACCDB are growing, this version includes the minimum # of objects (I hope at least) that are needed solely for executing query "21_qryUpd_ed_ing" (old) and "22_qryUpd_ed_ing_FirstWord" (new).

For purposes of solving/addressing the issue w/ the query, I purposefully removed all other objects. I hope I didn't remove some other requirement which is needed in order for "22_qryUpd_ed_ing_FirstWord" to work.

Thank you in advance,
Tom

[Gicu]

Hi Tom,

I did a (quick) glance over the files and I think nothing is wrong with the query or the logic... The only issue is that you used the 00_tbl_Source_Data after it was updated to present tense. So obviously there were no words ending in "ed" or "ing" because the code works so well so you will update 0 records; try on the "Original" copy and it should work as expected.

Cheers,

[skydivetom]

Vlad -- oh my gosh. I couldn't see the forest for the trees. Not making excuses but I guess I had stared at it for too long.

Query works great! Again, I apologize for prematurely asking the question (w/o taking proper breaks).

I will now start embarking of the full data set (1000s of records). I'll keep you posted about progress and final results.

Thank you all for the assistance thus far!

[skydivetom]

Vlad, Jack -- I promised I'd keep you posted on the final results. I'll summarize results in bullet format below:

BLUF (bottom line up front) -- this entire data cleansing process worked really well and I'm extremely happy with the outcome.

- While the automated procedures worked really well, the "one-time" *manual* evaluation of sentences took lit bit longer than originally anticipated.
- That is, given that many words were utilized either as verbs OR nouns, identifying the instances where word = verb was somewhat time-consuming.
- However, once I reviewed (and marked) the proper instances, the UPDATE function worked perfectly.
- Upon final review, I realized that I still had missed a few cases; however, updating them manually took less than an hour.

At this time, the 1000s of sentences have been standardized. From my perspective, the HR team should be very happy with the outcome.

Finally, as indicated in a previous post, this "text delimitation", "extraction" and "cleansing routine" will find application outside this particular project. So, I'm very happy to have it in my toolbox.

Again, thank you for all of the help you've provided in this post. I very much appreciate it!!

[orange]

[Gicu]

Thanks for the update Tom!