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

Welcome to UtterAccess! Please ( Login   or   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



Posts: 243
Joined: 22-July 09



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



Posts: 28
Joined: 6-August 08



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,335
Joined: 12-January 03
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), _
iddle_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
Go to the top of the page
 
hhh1027
post Jan 23 2010, 05:15 PM
Post#4



Posts: 243
Joined: 22-July 09



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



Posts: 243
Joined: 22-July 09



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,335
Joined: 12-January 03
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), _
en(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
Go to the top of the page
 
hhh1027
post Jan 23 2010, 06:22 PM
Post#7



Posts: 243
Joined: 22-July 09



Alan,
hould 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,335
Joined: 12-January 03
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
Go to the top of the page
 


RSSSearch   Top   Lo-Fi    2nd August 2015 - 03:21 PM