We help IT Professionals succeed at work.

How to parse Name field into separate fields

jaguar5554
jaguar5554 asked
on
70 Views
Last Modified: 2020-09-11
MS Access query to Parse string that may contain any or all of the following: title, first name, middle name or initial, last name, suffix, and/or credentials (Eg Dr John Jones or John J Jones M.D. or John Jones Or John Jan Jones, Dr. John J Jones MD, etc. )
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
For this i would suggest to use the Split Command and create a small function to return what you need
For example
Public Function SplitToElements(inputData As String)
Dim splitted() As String
splitted() = Split(inputData, " ")
For I = LBound(splitted) To UBound(splitted)
Debug.Print splitted(I)
Next
End Function
Feeding it with the 1st
?SplitToElements("Dr John Jones")
Dr
John
Jones
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
This function will do that:

Public Function SplitName(ByVal FullName As String) As String()

    Dim Parts           As Variant
    Dim Items(0 To 4)   As String
    Dim Item            As Integer
    Dim FirstItem       As Integer
    Dim LastItem        As Integer
    Dim MiddleItems()   As String
    Dim Label           As String
    
    Parts = Split(FullName)
    
    FirstItem = LBound(Parts)
    If Parts(FirstItem) = "Dr" Or Parts(FirstItem) = "Dr." Then
        Items(0) = Parts(FirstItem)
        FirstItem = FirstItem + 1
    End If
    LastItem = UBound(Parts)
    
    If Parts(LastItem) = "MD" Or Parts(LastItem) = "M.D." Then
        Items(4) = Parts(LastItem)
        LastItem = LastItem - 1
    End If
    Items(1) = Parts(FirstItem)
    Items(3) = Parts(LastItem)
    
    If LastItem > FirstItem + 1 Then
        ReDim MiddleItems(FirstItem + 1 To LastItem - 1)
        For Item = LBound(MiddleItems) To UBound(MiddleItems)
            MiddleItems(Item) = Parts(Item)
        Next
        Items(2) = Join(MiddleItems)
    End If
    
    For Item = 0 To 4
        Select Case Item
            Case 0
                Label = "Title"
            Case 1
                Label = "First Name"
            Case 2
                Label = "Initials"
            Case 3
                Label = "Last Name"
            Case 4
                Label = "Suffix"
        End Select
        Debug.Print Item, Label, Items(Item)
    Next
    
    SplitName = Items()
    
End Function

Open in new window

Will print for "Dr. John J Jones MD":

 0            Title         Dr.
 1            First Name    John
 2            Initials      J
 3            Last Name     Jones
 4            Suffix        MD

Open in new window

Output is an array. Typical usage:

Dim NameParts(0 To 4) As String

NameParts = SplitName("Dr. John J Jones MD")

NameParts(1) -> "John"
NameParts(3) -> "Jones"

Open in new window

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
The problem with trying to parse names, is that you never know, for sure how the name is constructed, is it last name first, does it contain prefix or suffix, ...

If you are trying to actually move data from a single field into multiple fields, then your best bet is to use some logic along with the split command. For example, if the Split() command splits the name into 4 or more segments, it is likely that there is either a prefix (Mr, Mrs, Miss, ...) or a suffix (Jr, MD, II, III, ...) .  I would probably create an array or table with these values and search to see if the first or last segment of the split returned one of these.

Then assume that the inner 3 items are First, MI, and Last

Good luck
jaguar5554Business Analyst

Author

Commented:
Good morning! I will work on this later this morning and let you know how I make out -- fingers cross!! Thank you so much in advance!
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Last note...if you want to "properly" map maybe you will need to create a mapping table
e.g.
MD
D.r.
D.r
..etc
so that when you do the splitting you try to identify the names from the Titles
Then single Letters should hold the Middle (father's) name..( i am not sure how this is handled in US)
and probably....just probably do an initial parse and hold the First Names as "John, George,Jim, Dale, Gustav "

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
jaguar5554Business Analyst

Author

Commented:
Thank you everyone. Unfortunately, I tried all of the solutions/recommendations provided and none worked. I either got "Undefined function [name] in expression" or the query would look to start running but after 45 minutes, still no progress in a query result. I am not familiar with programming so troubleshooting the provided solutions on my own is impossible! lol
Also, regarding Gustav's suggested code, if present, prefixes and suffixes can come in many different values and formats. Any other suggestions is greatly appreciated. Thanks  much-  
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, all we have is your sample data. We cannot guess what your data is like.

Also, in any case, this will require coding, so if you can't or won't manage that, team up with someone experienced who can help you out.
jaguar5554Business Analyst

Author

Commented:
My apologies if I offended you! It was certainly not my intent :-) I can usually manage some code but was not able to find a solution to the "undefined function" error I was getting. I will continue to work with the code you provided and perhaps get it to work  with my data. Thank you again-
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Oh, not at all, but you wrote:

I am not familiar with programming so troubleshooting the provided solutions on my own is impossible!

I thought you had given up on this. Indeed as their will be no simple or easy solution.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I'm pretty busy today, but I can work though this with you tomorrow.   As we have pointed out, this is not a simple task and  and it does rely on using code to perform the work.    

There's no way you are going to get anything in the query itself that will do the job.

 If you look at the article I posted, you see how complex the logic gets in trying to determine what a single field is made up of.

Jim.

jaguar5554Business Analyst

Author

Commented:
I'm just getting back to this and was glad to see both Gustav and Jim's responses. The issue at the time was overtaken with a dozen other data issues that also needed my attention! My current issue is drilling through a long text field to get a frequency on words. I found an article called Complex Text Filters on this site but cannot get any of the written functions to run through my data -- it may be missing a module. If you are familiar with a solution that will accomplish my need, anything you can provide or point me to is greatly appreciated. (Example, table with hundreds of thousands of rows, one of the fields is a long text field, function returns frequency on key words (e.g. camp, fitness, travel, etc.); otherwise, I continue to troll your website for assistance. Thanks again!!
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
@Jaguar, my complex text filters article is really more about searching large blocks of text (articles or documents) which are stored in a database.  It allows you to create a complex filter from an input that might look like:

Cancer + prostate - colon

to identify records which contain the terms "Cancer" and "Prostate", but not "colon", and this is actually a very simple example.  It really has nothing to do with parsing a single field which contains a variety of name parts, prefix, first, middle, last, suffix, certifications..

Dale
jaguar5554Business Analyst

Author

Commented:
Hello again Dale, thank you so much for responding! Although I added the comment to my question regarding parsing names, my current need is to create a lookup table of keywords and frequencies thereof from a long text field. I read your complex text filters article and downloaded your sample database and it is exactly what I am looking for -- I just don't know how to create the lookup table of keywords (in your database the table name is tbl_Word_Occurences) from my long text field. Is that something you can help me with? If so, I would be forever grateful! :-) Let me know... thank you much in advance- Andrea
jaguar5554Business Analyst

Author

Commented:
Understood and I will do so -- Thank you! (Note: I had also posted on Dale's ComplexTextFilters article but agree that it's best that I start a new thread). Thank you again- Andrea
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Andrea,

If I have a text field that I know contains 10 elements or less, I generally use a function ParseText() which looks like:
Public Function ParseText(ParseWhat as variant, Position as integer) as variant
     'I use a variant for ParseWhat on the off chance that the field being passed contains a Null
     'Position should be an integer between 0 and 9
    Dim strArray() as string
    Dim intLoop as integer


    ParseText = NULL
    if IsNull(ParseWhat) then exit sub
    if Position < 0 or Position > 9 then exit sub


    strArray = split(ParseWhat, " ")
    ParseText = strArray(Position)
End Function
I then have a table (tbl_Numbers) with one field (lngInteger) and values 0-9.  With this table I can create a cartesian join between the table with names and tbl_numbers, like this;
SELECT N.ID, N.FullName, lngInteger as Position, ParseText(N.FullName, lngInteger) as PosnValue
FROM tblNames as N, tbl_Numbers
WHERE ParseText(N.FullName, lngInteger) IS NOT NULL
ORDER BY N.FullName, lngInteger
for the value "Mr. Dale Smith" this would return
1, Mr. Dale Smith, 0, Mr.
1, Mr. Dale Smith, 1, Dale
1, Mr. Dale Smith, 2, Smith
With this query, you could then modify it from a SELECT query to either a make-table or append query to insert the PosnValue column into your Words table.  If your text field contains more than 10 elements, you can simply add records to tbl_Numbers or create a query (qry_Numbers) from tbl_Numbers that looks like:
SELECT Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones


jaguar5554Business Analyst

Author

Commented:
Thank you! I will try it out now! (fingers crossed).

jaguar5554Business Analyst

Author

Commented:
I adapted my table and field name to the code you provided but get the following error: Run-time error '9': subscript out of range. The following is an example of what I need the query to do:
ID 1   Traveled to Italy
ID 2   Traveled to Rome
ID 3   Traveled to Tuscany
ID 4   Traveled to Rome
ID 5   Traveled to Rome
ID 6   Traveled to Italy stayed in Rome and visited Tuscany. Ate at many restaurants and visited many museums.

Word   Frequency
Traveled   6
Italy          2
Rome       4
Tuscany   1
Restaurants   1
Museums   1
Visited   1
Ate   1
words like to, in, a, an, the, etc. would be filtered out

Is this something we can do? Thanks much- Andrea (heading home but will be back on-line in the morning. thank you thank you thank you!!)

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Andrea,  as I mentioned in my earlier post, I would run this query as an append query to a table that just contains a KeyWord column.  Something like:
INSERT into tbl_KeyWords (Key_Word)
SELECT ParseText(N.FullName, lngInteger) as PosnValue 
FROM tblNames as N, qry_Numbers 
WHERE ParseText(N.FullName, lngInteger) IS NOT NULL
Note the use of qry_Numbers above, instead of tbl_Number; is to accomdate those longer text values.  You will need to remove this line:
    if Position < 0 or Position > 9 then exit sub
from my original function as it is designed to ignore values greater than 9 and qry_Numbers contains values from 0 to 99. 

Then you can create an aggregate query which would group on Key_Word, filter out those values that you don't want (to, in, a, and, the, ...) and count the number of records with each of the other key_word values.

Regarding the error you are getting, try the following in the immediate window:
?ParseText("Traveled to Italy", 0)
?ParseText("Traveled to Italy", 3)
does either or both of those give you the error?

jaguar5554Business Analyst

Author

Commented:
Thank you Dale. I will try again (sorry for missing your original instruction)  I wish I had your brain! :-)

jaguar5554Business Analyst

Author

Commented:
Hello again,
?ParseText("Traveled to Italy", 3) returns the "out of range" error.
In the ParseText = strArray(Position) line, hovering over ParseText returns ParseText=Null and over strArray(Position) returns Position=3

I rem'd out the "if Position" line from the code you provided. Now working through the append query you suggest.... (thank you and I'll post again once I get something close to success! LOL) 



jaguar5554Business Analyst

Author

Commented:
Hello again, I wish I could see where my mistake is. I continue to get the "out of range error"; however, if I view the append query in datasheet view, #Name? displays in the PosnValue field. This is what my adapted SQL looks like:
INSERT INTO tbl_KeyWords KeyWord )
SELECT ParseText(N.MemoField,lngInteger) AS PosnValue
FROM tbl_Memos AS N, qry_Numbers
WHERE ParseText(N.MemoField, lngInteger) IS NOT NULL;
 
Any assistance is greatly appreciated!!

jaguar5554Business Analyst

Author

Commented:
oops.... typo in my previous email (KeyWord) -- but it's correct in my query :-)
Still staring at this database and no luck finding where it's going wrong.
I look forward to any help you can provide :-) Thanks much-
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Found a bug in the code I sent you, replace the previous version of the function with this:

Public Function ParseText(ParseWhat As Variant, Position As Integer) As Variant

    'I use a variant for ParseWhat on the off chance that the field being passed contains a Null
    'Position should be an integer between 0 and 9
    Dim strArray() As String
    Dim intLoop As Integer

    ParseText = Null
    If IsNull(ParseWhat) Then Exit Function
    if Position < 0 then exit function
   
    strArray = Split(ParseWhat, " ")
    If Position > UBound(strArray) - 1 Then Exit Function
    ParseText = strArray(Position)
   
End Function


jaguar5554Business Analyst

Author

Commented:
Holy cow. I did some research on the out of range error and read some posts regarding UBound but did not know how to apply. I had also tried the strArray - 1 but got very unexpected results (obviously). I will apply the new code and let you know how it works. Thanks much and stay tuned :-)
jaguar5554Business Analyst

Author

Commented:
Can you hear me cheering from my office?!  The code works with your ComplexTextFilter database beautifully. What a great tool. I cannot say "thank you" enough! Thank you!!

Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.