Need help w/ a query

[enthrop]

Hey,

First time posting here, so lets hope I get this right.

I've just recently begun to use Access & queries and such, and there's something I'm trying to figure out and I'm sure there's an easier way than what I'm doing, so was hoping someone could help me.

I have 2 fields I want to work with - one is where the data is coming from, another is what I want to update.

Data has stuff like:
VP of Sales
Sales VP
EVP Sales
Vice President Sales

then for IS/IT/MIS/Marketing/Operations/Telecommunications/Logistics/etc..

and the titles are in a million different random orders and methods (VP, Vice President, CFO, Chief Financial Officer, VP of Finance, VPFinance, Manager, Consultant, etc..)

What I've been writing are many many individual queries like:
("*VP*" or "*Vice President*&quot and ("*Sale*&quot
or
("*Manager*" or "*Mgr*&quot and ("*IT*" or "*IS*" or "*Information System*" or "*Information Tech*&quot

and now, my 2nd field, I want to update with "standarized titles" that all follow the same argument

So using the 2 queries above, the corresponding update fields:
"VP of Sales"
and
"Manager of IS/IT"

The problem is I have many databases I need to work with, and writing and importing 80 different small queries like I have above is simply impractical.

What's a better way I can do this? I tried putzing around a little with the script builder or something.. but I was too afraid to accidently mess up all my data (actually creating more backups as I type this just in case..)

Thanks in advance.

 

[enthrop]

Quick little update/additional question I forgot to ask:

I know Access has a little VB Builder (at least I think it does), unfortunately, I have no idea how to use VB.

As an extension of my question (and I am looking for the more efficient way to do the above), is there something equivalent in Access to like a program that would do what I want?

Like for example, I could declare 'structs' VP, Manager, C-lvl, etc.. and put in all possible permutations in each (ie VP would have VP, EVP, AVP, Vice President, etc..).

Then I could do the same for job functions (so a 'struct' IS would have IS, IT, Info Systems, etc..)

And do a search through each field first for what job title struct matches, then what job function matches, and according to that result, output say "VP of IS/IT"

Thanks.

 

[mpastore]

I think you are going to experience frustration trying to hard-code all strings that may possibly decode to something else.

A more labor-intensive but accurate solution lies in the use of crosswalk tables. Select distinct titles from your source tables in another table called xwalk_title (or xwalk_title, etc). Your disparate field values will be in field called field_raw. Create a new field called field_xwalk in the table that will translate raw values to a normalized value.

For example:

field_raw field_xwalk

VP of Sales VP of Sales
Sales VP VP of Sales
EVP Sales VP of Sales
Vice President Sales VP of Sales

If you have a lot of data, transfer everything to MS Excel to work on the field_xwalk values. Excel has smarts built into it to auto-complete values as you type so that if you have already added "VP of Sales" to a previous row and you type "VP" into a new row it will auto-complete for you. makes for pretty fast data input.

After you have done the data scrub in Excel, transfer everything back to MS Access.

Now you can use the crosswalk table to update your original table with normalized titles.


Mike Pastore

Hats off to (Roy) Harper

 

[enthrop]

Mike,

Thanks for the response, but I still have a problem.

What you described is basically what I _want_ to do (only you seem to have put it in a more easily understood way).

I actually do have a 3rd table w/ all the standarized titles - in the future when more data is added, people pick from a drop down menu that says what title the person has.

But since I'm converting the existing database - and there are over 400,000+ names, I just don't have the manpower to do much manually, thus me trying to find a more 'automated' process beyond what I'm already doing.

 

[mpastore]

Ok, plan B.

That's a lot of names. What you can also do is create a token table that contains key words and what they translate to. Problem that comes into play is your boolean logic, i.e. "VP" and "Sales" makes "VP of Sales".

With this in mind I would use VBA code to accomplish translations. So you would have something like:

update mytable set newtitle = title_trans(oldtitle)

You would have a function like this:


Function title_trans(psOldTitle As String) As String
' sample call:
' ?title_trans("Vice President of Sales&quot
Dim sUpperTitle As String

' clean up the title for compares
sUpperTitle = UCase(psOldTitle)
sUpperTitle = Replace(sUpperTitle, ".", "&quot
sUpperTitle = Replace(sUpperTitle, ",", "&quot

If InStr(sUpperTitle, "VP&quot > 0 _
And InStr(sUpperTitle, "SALES&quot > 0 Then
title_trans = "VP of Sales"
Exit Function
End If

If InStr(sUpperTitle, "VICE PRES&quot > 0 _
And InStr(sUpperTitle, "SALES&quot > 0 Then
title_trans = "VP of Sales"
Exit Function
End If

' etc, etc

title_trans = psOldTitle ' default return value, or make something like "???" if you want to whittle the list down

End Function


Advange of this approach is that all your logic will be easily viewable in one place. You can also clean-up the raw titles a bit (get rid of periods, make upper case) so that string comparisons will match more frequently. As you gain more experience with strin patterns which emerge, just add more IF statements to the code.

The disadvantage is that this will update slowly. Make this a luchtime process if running on 400k records. I would start out by taking 1000 or so records in a control set and trying to match as many raw string patterns there. You'd be suprised at how this will scale up to the 400k records. You may also want to use DAO recordsets to do a row-by-row update, but this is a more advanced topic that should be looked at if your query bogs down.

Mike Pastore

Hats off to (Roy) Harper

 

[enthrop]

Mike,

Thanks a lot of that tip. I understand what you suggested, and I'm about to go putz around and try it out.

I have smaller DBs I can work with first to try it out with, so lets see how this works out.

Thanks again.

- Alex

 

[enthrop]

Actually, I have a follow up question, and this is mostly due to my lack of experience with VBA (and Microsoft's Help isn't getting me far).

If InStr(sUpperTitle, "VP&quot > 0 _
And InStr(sUpperTitle, "SALES&quot > 0 Then
title_trans = "VP of Sales"
Exit Function
End If

If InStr(sUpperTitle, "VICE PRES&quot > 0 _
And InStr(sUpperTitle, "SALES&quot > 0 Then
title_trans = "VP of Sales"
Exit Function
End If

^^ Is what you have.
How would I modify it slightly so it's something like:
If (InStr("VP&quot || InStr("Vice Pres&quot) {
If (InStr("Sales&quot
.. = "VP of Sales"
If (InStr("IS" || InStr("IT&quot || ...)
.. = "VP of IS/IT"
}

If (InStr("Manager&quot {
....
}

can it do that?

 

[mpastore]

Uh, oh, we have a "C" programmer in our midsts!

If (InStr("VP&quot>0 or InStr("Vice Pres&quot>0) then
If (InStr("Sales&quot>0 then
.. = "VP of Sales"
end if

If (InStr("IS" >0 or InStr("IT&quot or ...) then
.. = "VP of IS/IT"

end if
end if

MS Access does not have control blocks. You have to use IF ENDIF nested blocks.



Mike Pastore

Hats off to (Roy) Harper

 

[enthrop]

Ok, I'm feeling very incompetent and ashamed for having to ask so many questions.. but I really don't have exp. w/ VBE and macros and stuff.. (well, now I know what I'll be doing tonight after getting home and digging out my Access books).

Anyway, I loaded up VBE and wrote a function: title_trans modelled after what you told me:

Function title_trans(psOldTitle As String) As String
' sample call:
' ?title_trans("Vice President of Sales&quot
Dim sUpperTitle As String

' clean up the title for compares
sUpperTitle = UCase(psOldTitle)
sUpperTitle = Replace(sUpperTitle, ".", "&quot
sUpperTitle = Replace(sUpperTitle, ",", "&quot

If (InStr(sUpperTitle, "VP&quot > 0 Or InStr(sUpperTitle, "Vice Pres&quot > 0) Then
If (InStr(sUpperTitle, "Sales&quot > 0) Then
title_trans = "VP of Sales"
End If

If (InStr(sUpperTitle, "IS&quot > 0 Or InStr(sUpperTitle, "IT&quot) Then
title_trans = "VP of IS or IT"
End If
End If

If (InStr(sUpperTitle, "Director&quot > 0) Then
If (InStr(sUpperTitle, "IS&quot > 0 Or InStr(sUpperTitle, "IT&quot) Then
title_trans = "Director of IS or IT"
End If
End If


'title_trans = psOldTitle
' default return value, or make something like "???" if you want to whittle the list down

End Function


My problem is - how do I get this to run?
I have table: tblNames
ANd 2 fields: strTitle and strStandardTitle

THe names are pretty much self explanatory - I want strStandardTitle to, at the end, reflect all the strTitle in the format described earlier.

I tried running an update query with title_trans([strTitle]) as the parameter, but that doesn't work. How do I do this?

 

[mpastore]

You need to create a new module and paste the code in there (save module as something like basTranslate). You can test the code by going to the command window and pasting:

?title_trans("Vice President of Sales&quot

You should see a return value of "VP of Sales" in the command window.

Once you are comfortable that this works, try it in a query:

update [tblNames] set [strStandardTitle]=title_trans([strTitle])






Mike Pastore

Hats off to (Roy) Harper

 

[enthrop]

THANK YOU SO MUCH!

I got it to work! so now I just have to build up the if-then statements and I can leave it running on the larger databases!

You've saved me so much time! thanks again!

 

[enthrop]

Haha. Don't you love how I keep posting follow ups?

I don't really need to know this - but it's more of the programmer of me coming out (and yeah I know, I used C above :/)

Is there a way to create an 'array' of all possible titles?
Ie VP[array_size] where each element of the array is a different variation of the title.

ie: VP[0] = "VP"; VP[1] = "Vice President"; etc..

And so for larger lists of job titles, instead of continuouly updating, I can do something like:

match = false;
for(int x = 0; x < sizeOf(VP); x++)
if (InStr(sUT, VP[x]) > 0) Then
match = true;


(wow that's bad pseudo code throwing together... some list of languages)

So instead of a IF for EVERY VP, I can just update one array and all subsequent usages of VP will refer to it

 

[mpastore]

Now you are getting somewhere.

redim aTokens(10) as string ' or any number
aTokens(1)=&quot;Vice President&quot;
sTokens(2)=&quot;Vice Pres.&quot;
etc, etc

for x = 1 to ubound(aTokens)
sToken = aTokens(x)
' do something with sToken
next x

Also, good practice to put Option Explicit statement at beginning of module. That enforces variable declaration before they are used, e.g. dim sToken as string. Reduces bugs.

Note, I'm not well versed on array searching in new versions of Access. In the past I have done a sort on the array and used a binary search algorithm (I don't have code). Post to the Access VBA or Visual Basic forums if you need pointers (no pun intended) on this.


Mike Pastore

Hats off to (Roy) Harper

 

[enthrop]

Thanks a bunch again Mike - I feel like I've spent my day thanking you

I've already started fooling around with what you gave me...

and I'm about to head off to the other forum you mentioned to see if what I have in mind is doable (pointers time!)

Thanks