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