VBA to move all values from n fields into new table with single field

[skydivetom]

Orange - wow!!!!



Your modification is a SIGNFICANT improvement. With the undesired characters (comma, periods, etc.) my actual raw data set contained nearly 4,200 keywords.

W/ your additional cleanup, an additional 1000 words were removed and I'm left with 3,207 keywords. BRILLIANT! That's is tremendously beneficial.

I wish to thank both of you for your superb expertise. I'll keep you posted on the next steps!!

[skydivetom]

June7, Orange:

Again, thank you helping me with yesterday's solution on how to split a field. Although I closed the thread, I was hoping I might get some assistance with a very **MINOR** tweak.

That is, when creating the destination table [01_tbl_WordDictionary_NoDups], I also would like to include the source's autonumber ID (w/o any mod). That will later on help me to cross-reference the original job title/sentence.

To accomplish this, I believe the VBA requires modifications in 2 places. Potentially, there may other places/lines as well.

#1 -- change from/to:
Set rs = db.OpenRecordset("SELECT Skill FROM 00_tbl_Source_Data")
Set rs = db.OpenRecordset("SELECT JobTitleID, Skill FROM 00_tbl_Source_Data") 'No issues when making this mod


#2 -- change from/to:
db.Execute "INSERT INTO 01_tbl_WordDictionary_NoDups([FoundWord]) Values('" & ary(x) & "')"
db.Execute "INSERT INTO 01_tbl_WordDictionary_NoDups([JobTitleID], [FoundWord]) Values(JobTitleID, '" & ary(x) & "')" 'Results in run-time error (too few parameters)

... where regular SQL (w/o manipulation) would follow the following syntax:

INSERT INTO 01_tbl_WordDictionary_NoDups ([JobTitleID], [FoundWord])
SELECT [00_tbl_Source_Data].JobTitleID, [00_tbl_Source_Data].Skill
FROM 00_tbl_Source_Data;

Any thoughts on how the line "db.Execute "INSERT INTO..." needs to be tweaked so that I also add the [JobTitleID] into the destination table?

[orange]

?? What if 2 or more records have the same value--which recordid should be recorded?
I haven't looked at your zip yet.

Second thought, if you are getting unique words, then only the first occurrence will be recorded. Implies you'll get the recordid where the "word" is found first. So if there were 5 occurrences of "Analytic", you will only record the first occurrence and associated recordid.

How do these things modify your requirement?

[skydivetom]

Orange -- yes, I totally overlooked that. Now that you brought this up, the ideal scenario would be to change the ID field to a text field and then adding the reference number. For example, 7, 13, 28. But that completely changes the process and it's way beyond the "minor" tweak.

That said, I may have to work w/o the ID number. If you have another thought which is doble, I'm open to suggestions.

[orange]

I have been playing with the data.
I removed/commented some of the code.
Attached is a list of distinct words and the lowest JobtitleID.
it's a start. Please advise on usefulness or adjustments required.

[skydivetom]

Orange:

Thank you for that list... I think it will be very helpful. I remember from previous posts -- related to "dealing with data cleanup" -- you always had a very keen eye and forward-leaning approach WRT solutions.

In order to answer your question, I will have to provide more info on the next steps. I am fully aware that these "next steps" are completely above and beyond the original requirements. @June7 -- feel free to ignore the below (you helped me solve the original problem).

I will attempt and refrain from going into too many details...

High-level summary of next steps:
- Table [00_tbl_Source_Data] contains job descriptions for various positions.
- The "action verbs" (design, develop, manage, etc.) are written in different tenses (past, present, future). Ultimately, I would like to standardize all actions verbs in present tense.
- Given that I have thousands (1000s) of job descriptions, a manual approach is not feasible. This is where I hope VBA can be brought into the picture and **maybe** use a lookup table to transform any actions words not written in present tense.

Challenge:
- Please see attached DB containing initial stab at reviewing the distinct words based on 40 sample job descriptions.
- I have NOT fully determined how to approach this process but I started with identifying which extracted words are truly verbs. I checked them in Boolean field [Verb].
- Now, within the context of the job description, some action verbs could be also considered nouns. For example, w/o looking at the job description, is word = "documents" a verb or a noun?
- Without the context of the source record... the answer has to be "I don't know!" That is, an employee "documents" (verb) requirements. Alternatively, an employee may need to update "documents" (noun). So, for now, these are checked in Boolean fields [Verb] and [Verb_NonVerb].
- Finally, all other words (regardless of their type) are checked in field [NonVerb]. These are clearly non action verbs and can be dismissed.

At the risk of providing too much information in this thread, I will stop here. Again, the above summary/challenge should be really part of a new thread.

However, in order to address your (@Orange) comment "Please advise on usefulness or adjustments required.", I felt it would be best to share w/ you a deeper vision. Not only does that prevent you from working on something that may not be in support of the ultimate product but it also helped me get things more straight in my mind.

Again, thank you for taking an additional stab at the code. I'd love to see the mods you made in the VBA module.

Cheers,
Tom

[skydivetom]

Orange -- good morning. Not sure if you saw my previous post.

How did you modify the VBA so that the 1st occurrence of the JobID is inserted?

[orange]

Hi Tom,

I started with your database in Modification.zip. But didn't know your logic with the Temp table so bypassed some code and that table. I extracted the foundwords and JobTitleIds into 01_tbl_worddictionary_nodups --But I had dups obviously. I created a table Jack that had Indexed no Duplicates on Foundword. I have an append query QueryJack

INSERT INTO jack ( jobtitleId, foundword )
SELECT [01_tbl_WordDictionary_NoDups].jobtitleId, [01_tbl_WordDictionary_NoDups].foundword
FROM 01_tbl_WordDictionary_NoDups;

That populates JACK with the foundwords from 01_tbl_WordDictionary_NoDups. Since the table doesn't allow duplicates, only the first record gets added. The rest are rejected because of the No duplicate constraint.

This morning I looked at creating a concatrelated based query to identify in which JobTitleIds each Foundword occurred. Here's a sample.

foundword	Expr1001
a	11, 14, 21, 22, 38
ability	18, 38
access	9
ACT	11
actions	8
ACT-wide	11
address	11
advanced	20
advised	9
an	14
analysis	8, 18, 18, 20, 21, 22
analysts	39
Analyze	1
and	1, 1, 1, 3, 3, 6, 8, 9, 10, 10, 12, 13, 15, 16, 16, 17, 17, 17, 18, 20, 20, 20, 21, 22, 22, 23, 23, 24, 25, 25, 27, 28, 29, 30, 31, 32, 35, 35, 36, 37, 38, 38, 38, 39, 39, 40, 40
and/or	2, 35
application	12, 12, 15
applications	20
Apply	2
architecture	16
area	40
as	4, 4, 32, 38, 38
Assessed	3
assigned	10, 40
assignments	37

However, I noticed an issue. There are different quote characters in your data. I'm not sure of the extent yet, but it is playing havoc with the Concat stuff. I'm not sure how far back I have to go to remove the "strange quote".

To use ConcatRelated, I created a query "Query1" which was distinct Foundwords. Then used a second query "FoundWordsInJobTitleIDQ".

SELECT Query1.foundword, Concatrelated("Jobtitleid","[01_tbl_WordDictionary_NoDups]","foundword ='" & foundword &"'")
FROM Query1;

This now goes screwey on Program's and project’s (Notice the different quotes). It could be an issue with the ConcatRelated function as well- not sure ( I see different quotes, but quotes regardless may be an issue).

I haven't really looked at your latest post. Just thought I'd pass this along.
Please post any thoughts.

[skydivetom]

Jack -- that looks very promising. I'd love to see how this is generated via the VBA. Would you mind sharing the last ACCDB version?

Thanks,
Tom

[orange]

Tom,
Attached in zip format is the FoundWord material.

NOTE: There may be an issue with quotes in field values when using ConcatRelated.
I modified my "FoundWordsInJobTitleIDQ" to ignore foundword with a quote.
Also I did a replace in the extract to change project’s to project's, but ignore values with quotes as mentioned.

...........
 For x = 0 To UBound(ary)
        ary(x) = Replace(ary(x), "'", "''")  'quote to double quote
        ary(x) = Replace(ary(x), "’", "''")  'bad quote to double quote
        ary(x) = Replace(ary(x), ".", "")    'remove "."
        ary(x) = Replace(ary(x), ",", "")    'remove ","
        ary(x) = Replace(ary(x), "(", "")   'remove "("
        ary(x) = Replace(ary(x), ")", "")   'remove ")"
.........

SELECT Query1.foundword
, Concatrelated("Jobtitleid","[01_tbl_WordDictionary_NoDups]","foundword ='" & foundword & "'") AS Expr1
FROM Query1
WHERE (((Query1.foundword) Not Like "*'*"));

[skydivetom]

Jack:

I'm loving the this next-step solution. As part of my becoming better familiarized with the process, I've taken the liberty to merely rename some of the objects.

Please see attached JPG illustrating the before/after object renaming.

Furthermore, I made the following changes:
1. Added query "02_qry_WordDictionary_NoDups" (previously "QueryJack") to the module so it is automatically updated.
2. For evaluation purposes only, I reduced the source data and now use only 10 sample job descriptions.
3. Also, for testing only, I significantly abbreviated the job titles. Naturally, this results in fewer records to review/evaluate.

Before I go on to work on "next steps", I was wondering if the order of JobTitleID in query "04_qry_FoundWordInJobTitleIDQ_Output" (field "ConcatID") can be changed to ASC order.

For example, the word "Analyze" shows ConcatID equal to: 2, 2, 3, 1.

Now, in review of the original source data, "Analyze" can indeed be found a) twice in the second record as well as b) once in the third record and c) once in the first record.

However, as I may have to review the context of 1000s of records, it would be easier if ConcatIDs would be in order: 1, 2, 2, 3

Alternatively, the word "analyzing" is shown in the correct order: "3, 4".Not, I'm not entirely clear as to how they are being appended, but having all listed in ASC order would be great.

Is that possible? If so, how would be code have to be modified to achive the latter?

Again, thanks for this great next step method. Is is extremely helpful!!!

Cheers,
Tom

[orange]

Hi Tom,

I'll take a look. The output is directly from Allen Browne's ConcatRelated function. As for naming- it's your project and data so do what's best for you. As long as you provide e with instructions and data, I can usually concoct something.
We're getting about 16-20" of snow at the moment, so it may be later tonight that I get to look at this.

You'd like to see the job ids in ascending order - how about only once per job id regardless of how many occurrences(just a thought).
I may check the quote thing in the function also.

[skydivetom]

Jack -- excellent. And, yes, the instructions (renaming from/to) are provided in the JPG (post #26).

And, yes, showing only, e.g., "1, 2, 3" (vs. "1, 2, 2, 3") for the 1st record would be even better.

//

Urgh... I hope the accumulated snow won't result in any serious issues in your area. Stay safe!!

[orange]

Tom,
After reading the ConcatRelated description the sort order can be accommodated. The SortOrder is parameter 3.
See this: Last parm is JobtitleID eans it is the field to sort result on.
I called this Query2

SELECT Query1.foundword
, Concatrelated("Jobtitleid","[01_tbl_WordDictionary_NoDups]","foundword ='" & foundword & "'","jobtitleId") AS ConCaTEst
FROM Query1 to test
WHERE (((Query1.foundword) Not Like "*'*"));

Works for me. I did not see a way to prevent duplicates usages, but it may not be required.(certainly isn't one of the standard options)

[skydivetom]

Jack -- your solution to this is brilliant!! And, the duplicates usages are perfectly ok.

Thank you so very much for your assistance and patience to get to this current (critical) point in the automation routine. The baseline allows me to now easily cross-reference words (and verify their context) which will make the manual evaluation of word (verbs) much easier.

As you know, your additional help (over the last 2 days) went way beyond the requirements listed in the original thread. Thus, for the next phase (actual updating of words) I could not see to continue here. Instead, I opened a new post and provided you/other users a summary of the current process as well as the "next steps".

Given your expertise of text/data manipulation, I am hoping you have continued interest in the final solution. Either way, I'm extremely grateful for the assistance that you (as well as June7) have given me in this thread. THOUSAND THANKS!!!!

https://www.accessforums.net/showthr...108#post489108