Is what I want to do possible...? 1

[enthrop]

Hey,

I've been putzing around w/ my data (asked around in Queries Forum), and the suggestion presented was a VB module. I've coded a very basic one and got it working, but because I plan on updating my data and things might get changed, I need a VB module that is also easily updated to be able to keep up to date with my data.

This module we at the end be run on 400,000+ pieces of data, so I've been starting slow and building up, testing it on 50-200 data at an instance.

The gist of what I want is to convert from a Title string to a Standard Title, dependant on job role.

Example:
Title String Output (Standard Title)
Sales, Manager Manager of Sales
Director, IS Director of IS or IT
Mgr of Sales Manager of Sales
VP IT VP of IS or IT
Vice Pres Market VP of Marketing

So basically, I'm "standarizing" everything. VP/Vice Pres/AVP/EVP all becomes VP. Department like Customer Service, Customer Svc, Cust Svc, etc.. will all become like "VP of Customer Service"

So right now I have a bunch of IF-THENs:

If (InStr(sUT, "CUSTOMER SERVICE&quot > 0 Or InStr(sUT, "CUSTOMER SVC&quot) Then
If (InStr(sUT, "VP&quot > 0 Or InStr(sUT, "VICE PRES&quot > 0) Then
title_trans = "VP of Customer Service"
End If
If (InStr(sUT, "DIRECTOR&quot > 0) Then
title_trans = "Director of Customer Service"
End If
If (InStr(sUT, "MANAGER&quot > 0 Or InStr(sUT, "MGR&quot > 0 Or InStr(sUT, "GM&quot > 0) Then
title_trans = "Manager of Customer Service"
End If
End If


But obviously then, if I find another variation of say "DIRECTOR" (ie "DIR&quot, then I have to go to EVERY IF look and update it (and I have nested IF loops within every department).

So I figured I'll use an ARRAY and see if I can get this working.. but here I'm stuck 'cause I don't know the limitation of Access DB (I'm a Java, C, non VB user)

What I want to do is something like this:
Have arrays: VPArray, DirectorArray, ManagerArray
where each array will contain all possible variations of the job title
ie VPArray[1] = "VP"; VPArray[2] = "Vice President", etc..

This is when it gets tricky and I don't know if this is doable. If this was C, I would create another Array Department, with Pointers to LinkedLists for each department.

so like:

department[1] --> customer_service, where customer_service[1] = "CUSTOMER SERVICE", customer_service[2] = "CUST SERV"
department[2] --> IS, where IS[1] = "IS", IS[2]= "IT", etc..

Then I would put everything together with for/if/while loops (pseudocode with several diff languages):

// Picks what title the person is
for (each of the different title arrays)
for (int x = 0; x < length(titleArray); x++)
if (InStr(titleString, titleArray[x])
String stdTitle = titleArray[1];

// Find out what department he is
for (int y = 0; y < length(departmentArray); y++)
for (each element in departmentArray, go to all the nodes in the LL)
if (InStr(titleString, string in node)
String stdDept = departmentArray[y].dept

and then combine stdTitle w/ stdDept
------
Does anyone understand what I'm saying or am I being vague? Hehe.

Anyway, is what I want to do possible? And/or does anyone see an easier way for me to do this?

 

[SBendBuckeye]

How about limiting yourself to 2 arrays, a title array and a departmental array since presumably a person can only have one title at a time? Then you could process each piece separately and combine them when complete.

Try something like the following:

1. Define module level private dynamic array variables

Private m_avarTitleArray As Variant
Private Const ABBREV = 0
Private Const TITLE = 1

Private m_avarDeptArray As Variant
'Private Const ABBREV = 0 For documentation only
Private Const DEPT = 1

2. Build your arrays as you wish

Dim lngIndex As Long

If IsMissing(m_avarTitleArray) Or _
m_avarTitleArray = Nothing Then
Redim m_avarTitleArray(1, lngIndex)
Else
lngIndex = lngIndex + 1
Redim Preserve m_avarTitleArray(1, lngIndex)
End If

m_avarTitleArray(ABBREV, lngIndex) = &quot;VP&quot;
m_avarTitleArray(TITLE, lngIndex) = &quot;Vice President&quot;

Similary with the Department array

3. Build AScan functions to return new title/department and invoke as below

Dim strDept As String
Dim strTitle As String
Dim strAbbrev As String
Dim strNewTitle As String

'Test allows you to do in pieces and skip unchanged
strAbbrev = InputTitleValueFromSomewhere
strTitle = AScanTitle(strAbbrev)
If strAbbrev <> strTitle Then
strAbbrev = InputDeptValueFromSomewhere
strDept = AScanDept(strAbbrev)
If strAbbrev <> strDept Then
strNewTitle = strTitle & &quot; of &quot; & strDept
End If
End If

End Sub or Function

Private Function AScanTitle(pstrSearch As String) As String
Dim lngIndex As Long
'Scan the title array searching for a match
For lngIndex = 0 To UBound(m_avarTitleArray, 2)
If InStr(1, m_avarTitleArray(ABBREV, lngIndex), _
pstrSearch, vbDatabaseCompare) > 0 Then
AScanTitle = m_avarTitleArray(TITLE, lngIndex)
Exit Function
End If
Next lngIndex
'Only get here if nothing found
AScanTitle = pstrSearch
End Function 'AScanTitle

Create a similar AScanDept

Hope this helps and good luck!

 

[MichaelRed]

I think i would go about it quite differently.

Starting from some position, you would obtain a list of all the current &quot;Title String&quot; entries. Parse ALL (perhaps using &quot;Split) to obtain the individual words. Remove any (all) trivial words and punctuation (&quot;conjunctions&quot;, etc -such as [the | of | and | for | ... ]. Generate the list of UNIQUE remaining &quot;words&quot;. Generate a Xref 'Map' for your combined terms, so that all hte variations of &quot;VP&quot; simply point to the real &quot;VP&quot; (or &quot;Vice President&quot; term). Do at least a cursory mechanical review and 'categorize' these according t the 'type' ([title | dept | function | ... ]. Review your schema and determine wheather it can / will accomodate the overall process.

In MANY of the generic search and replace processes, the number of POSSIBLE conditions become large enough to be 'cumbersome' to do in the mein of an extended IF block structure. Often the complexity is somewhat reduced with Select Case structures, but many -in the end- are smply not worth the effort to achieve the 100% conversion. 'Ye Olde 80-20 rule' become MY personal crutch in some of these situations, so buile the routine to simply OMIT conversions which do not fit into the schema you devise. When the procedure is complete, just review the empty (new) titles if there are not to many, fix the manually, otherwise review / revise the mapping and parsing to deal with them (or just generate another -perhaps smaller one -after allit should not need to 're-do' the completed ones - unless you find you need to make some changfes there as well.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places