UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> How to split first name last name and middle initial in query    
 
   
hhh1027
post Jan 23 2010, 02:47 PM
Post #1

UtterAccess Addict
Posts: 243



Hello,

I have table where last name, first name and middle initial are in one column and I would like to split in three, here is sample how it is now.

Person_Name = Smith, John M

and I would like to see this way

First_Name = John
Last_Name = Smith
Middle_Initial = M

Please help thanks
Go to the top of the page
 
+
Kristoph
post Jan 23 2010, 04:12 PM
Post #2

UtterAccess Member
Posts: 29



Hello hhh1027,

My suggestion is likely not the easiest way, and does not involve a query - an expert in this community will likely offer a better way to do this.

You can:
1) Export the table to Excel
2) Insert a few extra columns behind the Person_Name column
3) Highlight Person_Name column
4) Choose Data ---> Text to Columns ---> Delimited ---> Space
5) Rename your newly created columns, First_Name, Last_Name, Middle_Initial
6) Delete any extra commas using find/replace
7) Import back into Access

There are also some previous posts here that are similar to your goal. Here is an example:
http://www.utteraccess.com/forums/showflat...;Number=1909090

Hope this helps,
-Kristoph
Go to the top of the page
 
+
Alan_G
post Jan 23 2010, 04:48 PM
Post #3

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,116
From: Devon UK



Hi

You could use an update query. As long as all of your data is in the format of your example (which I guess is unlikely?) then the query would be along the lines of

CODE
UPDATE YourTable SET First_Name = Left([Person_Name],InStr([Person_Name],",")-1), _

Middle_Name = Right([Person_Name],1), Last_Name = Left([Person_Name],InStr([Person_Name],",")-1)


You problems would arise with anyone without a middle initial, or someone with a different name - eg Mark De Sylva or Ian St. John etc


--------------------
HTH
Alan

It's nice to be important, but it's more important to be nice
UA Wiki Article ToC | UA Wiki Function Library ToC
Go to the top of the page
 
+
hhh1027
post Jan 23 2010, 05:15 PM
Post #4

UtterAccess Addict
Posts: 243



The file too large to export to Excel.

Thanks
Go to the top of the page
 
+
hhh1027
post Jan 23 2010, 05:31 PM
Post #5

UtterAccess Addict
Posts: 243



There some names without middle initials
Go to the top of the page
 
+
Alan_G
post Jan 23 2010, 06:16 PM
Post #6

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,116
From: Devon UK



Hi

Figured there might be wink.gif

Also, please ignore my suggested query above (not been a good day today!!) which isn't correct even if all the names had a middle initial. It should be more like

CODE
UPDATE YourTable SET First_Name = Left(Mid([Person_Name],InStr([Person_Name]," ")+1), _

Len(Mid([Person_Name],InStr([Person_Name]," ")+1))-2), Middle_Name = Right([Person_Name],1), _

Last_Name = Left([Person_Name],InStr([Person_Name],",")-1)


The answer for your question I think would be a User Defined Function that would have to handle the logic taking into account the names with/without a middle initial (and possibly any out of the ordinary names) that you could then use in an update query


--------------------
HTH
Alan

It's nice to be important, but it's more important to be nice
UA Wiki Article ToC | UA Wiki Function Library ToC
Go to the top of the page
 
+
hhh1027
post Jan 23 2010, 06:22 PM
Post #7

UtterAccess Addict
Posts: 243



Alan,

Should I do this as update query?
Go to the top of the page
 
+
Alan_G
post Jan 23 2010, 06:33 PM
Post #8

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,116
From: Devon UK



Hi

If you're using my example (which doesn't take into account names without middle initials) then it is an update query already. Copy and paste it into the SQL view of a new query and change the names I've used to your actual names.

CAUTION - try it on a back up of your data first, but again, it doesn't take into account names without middle initials or names like Mark De Sylva or Ian St. John. You'd have to write a UDF to handle all of the logic for that wink.gif


--------------------
HTH
Alan

It's nice to be important, but it's more important to be nice
UA Wiki Article ToC | UA Wiki Function Library ToC
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 24th July 2014 - 09:44 AM