Query to change order of First name(s)+ Last Name to Last Name, First 1

[DarleneN]

We have a huge data base which includes the following fields: [First+LastName] and [TrustorName].

The names are in random format as these examples show:

Tammie L. Johnson
Linwood C. III & Vickie J. Knowles
Hillard Hamm
Luis & Maria G. Sais
Jim Smith & Mary Smith

Often the [First+LastName] field has the text “SAME” representing that the data is the same as in the [TrustorName] field.

I need some help in writing an update query that will convert the above names to a
LastName, FirstName format and put the new format back into the [First+LastName] field.

Johnson, Tammie L.
Knowles, Linwood C. III & Vickie J.
Hamm, Hillard
Sais, Luis & Maria
Smith, Jim & Mary

Also if “SAME” is found, I would like the same conversion to look at [TrustorName] field and update the [First+LastName].

The most critical issue is to have the "LastName, " and then the balance of the FirstName data. Example: I could live with Smith, Jim Smith & Mary

Anyone have any ideas that would help me?

Thanks

Dar

 

[scriverb]

What version of ACCESS are you using?

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Access 2K+ has a Function called InStrRev. A97 doesn't have this function but the InStrRight Function below can be substituted. Copy and Paste this Function into a database module:
Public Function InStrRight(vSearchStr As String, vTargetStr As String, vStart As Long) As Long
Dim i As Integer
For i = vStart To 1 Step -1
If InStr(i, vSearchStr, vTargetStr, 1) = i Then
InStrRight = i 'Position of vTargetStr
Exit For
End If
Next i
End Function

It seems all of the formats have the Last name at the end of the text string. Using that a rule to the formating the following query SQL will turn this string around to the desired format:
UPDATE tblYourTableName as A SET A.[First+LastName] = Mid$(A![First+LastName],InStrRight(A![First+LastName]," ",Len(A![First+LastName]))+1) & ", " & Mid$(A![First+LastName],1,InStrRight(A![First+LastName]," ",Len(A![First+LastName]))-1);

Let me know if you want me to use the A2k function rather than this one.



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MichaelRed]

I would be somewhat more cautious/conservative. Instead of actually updating the field, I would create tow (or more) seperate fields and seperate the current field into as many component parts as reasonable / desired, then re-assemble them dynamically (at run time) via concateneation.

From experience in the 'name game', I have found that "full" names come with more variation than I am able to programatically deal with, so the best approach I was able to devise was the 'divide and conquor' approach. Seperate each "word" in the name and attempt to categorize it as some part of the structure you desire (NameL, NameF, NameM, Salutation, Address, Title, Descendent, spouse, ...). Re-assemble the package (vis SELECT queries) and have the results CAREFULLY reviewed and adjusted to reflect the actual FULL name in the appropiate format(s). Then (and only then) switch the app over to the use of the name parts -including the input data to the extent possible.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[r937]

excellent advice, michaelred, worth a star

rudy

 

[scriverb]

I agree with MichaelRed in that you should be very cautious about the final update of information when you intend to overwrite the original data. The use of seperate fields in a new table with each piece of the name seperated is the ultimate way to go. You should never update your original data without a backup copy so that if the results are not what you wish you can backup and start over. That said you asked for an UPDATE query to perform a certain function. The following will do just that and will include the instances of finding the word SAME in the [First+LastName] field:

UPDATE tblYourTableName as A SET A.[First+LastName] = IIF(A.[First+LastName] = "SAME", Mid$(A![TrustorName],InStrRight(A![TrustorName]," ",Len(A![TrustorName]))+1) & ", " & Mid$(A![TrustorName],1,InStrRight(A![TrustorName]," ",Len(A![TrustorName]))-1) ,Mid$(A![First+LastName],InStrRight(A![First+LastName]," ",Len(A![First+LastName]))+1) & ", " & Mid$(A![First+LastName],1,InStrRight(A![First+LastName]," ",Len(A![First+LastName]))-1));

Remember that this Update query is using the Rule that you have provided as to the formatting of this field. LastName will be at the very end of the string. There are instances where names like St. Johns where only Johns will be identified as the LastName. You see we are looking for the first instance of a space starting at the end of the string and moving towards the beginning.

Like we all agree be careful when performing these type operations.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[DarleneN]

Thank you Bob and Michael. Your insight has been a help. I will use Bob's ideas to initially sort the information and then manually look it over for obvious errors. I found many such instances such as last name "De La Para" etc. and those that were business names. I will manually adjust them to work with the formula prior to actually runing the update.

I am able to modify our data input for all new records so that the first and last names will be saved as separate fields from now on. Hence the manual update will only have to be completed once.

Thanks soo much. It was nice finding your responses first thing today.

Dar

 

[DarleneN]

Bob, I tried your last suggestion substituting InStrRev for InStrRight (I am using Access 2000) and receive an error message when I try to run the update (on a backup table). "undefined function InStrRev in expression.

Do I need to do something with the public function? Are there additional modules to add from the setup disk?

Dar

 

[scriverb]

Check your Library References. There must be a library that is not selected or missing.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???