UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
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: 28



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,230
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

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,230
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

UtterAccess Addict
Posts: 243



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,230
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
 
+

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: 31st October 2014 - 03:21 AM

Tag cloud: