A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Switch Last Name First Name to First Name Last Name



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 12:35 AM posted to microsoft.public.access.queries
Iram
external usenet poster
 
Posts: 122
Default Switch Last Name First Name to First Name Last Name

Hello,

Access 2003. I have a single field called "Name" in a table called "Members".

There is about 300 records in a Last Name First Name format, example

Doe John
Moore Dave
Johnson Jacob Mario

(Some names have Middle Names or Sir's)

How could I run a query to change the names to First Name Last Name? Or if
you recommend in Excel how would I do this?

John Doe
Dave Moore
Jacob Mario Johnson



Thanks.
Iram
  #2  
Old June 4th, 2010, 01:38 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Switch Last Name First Name to First Name Last Name

On Thu, 3 Jun 2010 16:35:15 -0700, Iram
wrote:

Hello,

Access 2003. I have a single field called "Name" in a table called "Members".


Ouch. Name is a reserved word - a table has a Name property, a field has a
Name property, a form control has a Name property... Access can and will get
confused! I would very strongly recommend revising your table structure to
have separate fields for the components of the name: FirstName, MiddleName,
LastName, Suffix (e.g. Jr., III).

There is about 300 records in a Last Name First Name format, example


Good that there are not too many, this can be a fair bit of work to handle the
exceptions.

Doe John
Moore Dave
Johnson Jacob Mario


How about Mary Jo Johnson (first name Mary Jo, just ask her); or Hans ten
Broek (first name Hans, last name ten Broek); or Wing Men Li (you'll have to
find out whether he's using the Chinese tradition of family name first or has
turned it around and is actually a member of the Li family).

(Some names have Middle Names or Sir's)


Sir Richard Featherstonehaugh Wembley-Fawkes III.... shudder

How could I run a query to change the names to First Name Last Name? Or if
you recommend in Excel how would I do this?

John Doe
Dave Moore
Jacob Mario Johnson


I'd do this in a series of passes. Add the additional fields suggested, or a
reasonable variant thereof. First run an Update query:

UPDATE table
SET FirstName = Left([Name], InStr([Name], " ") -1), LastName = Mid([Name],
InStr([Name], " ") + 1)
WHERE [Name] LIKE "* *";

THis will parse out all the simple two-word names. With only 300 I'd then just
run a query with a criterion

LIKE "* *"

on LastName to select three- or more-word names; you can manually edit them.
Or you can run an analog of the query above to populate middle and last name,
and then carefully edit the records.
--

John W. Vinson [MVP]
  #3  
Old June 4th, 2010, 04:22 PM posted to microsoft.public.access.queries
Iram
external usenet poster
 
Posts: 122
Default Switch Last Name First Name to First Name Last Name

Thank you John W. Vinson!



"John W. Vinson" wrote:

On Thu, 3 Jun 2010 16:35:15 -0700, Iram
wrote:

Hello,

Access 2003. I have a single field called "Name" in a table called "Members".


Ouch. Name is a reserved word - a table has a Name property, a field has a
Name property, a form control has a Name property... Access can and will get
confused! I would very strongly recommend revising your table structure to
have separate fields for the components of the name: FirstName, MiddleName,
LastName, Suffix (e.g. Jr., III).

There is about 300 records in a Last Name First Name format, example


Good that there are not too many, this can be a fair bit of work to handle the
exceptions.

Doe John
Moore Dave
Johnson Jacob Mario


How about Mary Jo Johnson (first name Mary Jo, just ask her); or Hans ten
Broek (first name Hans, last name ten Broek); or Wing Men Li (you'll have to
find out whether he's using the Chinese tradition of family name first or has
turned it around and is actually a member of the Li family).

(Some names have Middle Names or Sir's)


Sir Richard Featherstonehaugh Wembley-Fawkes III.... shudder

How could I run a query to change the names to First Name Last Name? Or if
you recommend in Excel how would I do this?

John Doe
Dave Moore
Jacob Mario Johnson


I'd do this in a series of passes. Add the additional fields suggested, or a
reasonable variant thereof. First run an Update query:

UPDATE table
SET FirstName = Left([Name], InStr([Name], " ") -1), LastName = Mid([Name],
InStr([Name], " ") + 1)
WHERE [Name] LIKE "* *";

THis will parse out all the simple two-word names. With only 300 I'd then just
run a query with a criterion

LIKE "* *"

on LastName to select three- or more-word names; you can manually edit them.
Or you can run an analog of the query above to populate middle and last name,
and then carefully edit the records.
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.