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
|
|
0
|
|
|
|
Reply
|
 Utf
|
6/3/2010 11:35:15 PM |
|
On Thu, 3 Jun 2010 16:35:15 -0700, Iram <Iram@discussions.microsoft.com>
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]
|
|
|
0
|
|
|
|
Reply
|
John
|
6/4/2010 12:38:57 AM
|
|
Thank you John W. Vinson!
"John W. Vinson" wrote:
> On Thu, 3 Jun 2010 16:35:15 -0700, Iram <Iram@discussions.microsoft.com>
> 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]
> .
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
6/4/2010 3:22:24 PM
|
|
|
2 Replies
1198 Views
(page loaded in 0.002 seconds)
Similiar Articles: Switch Last Name First Name to First Name Last Name - microsoft ...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... convert from last name, first name TO first name, last name ...... all of them at once0 from the last name, first name ... Switch Last Name First Name to First Name Last Name - microsoft ..... names have Middle Names or Sir's) Sir ... Switch First and Last Names - microsoft.public.excel.worksheet ...Switch Last Name First Name to First Name Last Name - microsoft ..... names have Middle Names or Sir's) Sir Richard Featherstonehaugh Wembley-Fawkes III.... <shudder ... Parse Name Field into First Name Last Name - microsoft.public ...Switch Last Name First Name to First Name Last Name - microsoft ..... will ... The CONTACT field contains both the first and last names ... the actual first and last name ... Extracting First Name from cell with first name and last name ...Switch Last Name First Name to First Name Last Name - microsoft ... Extracting First ... Excel - Last Name, First Name Extract - Column A has names ... Last Name, First Name ... Separate By Comma - microsoft.public.access.queriesHello, I have one field that has a comma between the first and last name. The rest of the fields are fine. Is tehre any way to separate the first an... How do I combine "First Name", "Last Name" ...I have two tables one has First Name and Last Name fields. The other has first and last names combined in a ... Switch Last Name First Name to First Name Last Name ... Spliting First Middle and Last Name - microsoft.public.access ...If the last and first names are always separated by a /: LastName: Left ... Switch Last Name First Name to First Name Last Name - microsoft ... Spliting First ... Sorting by Last Name and then First Name in the Same Field ...... inherited" a database where the first name and last ... Switch Last Name First Name to First Name Last Name ... First Name", "Last Name" ..... names into first name, last ... How concatenate firstname and lastname in one report control ...How concatenate firstname and lastname in one report control ... Switch Last Name First ... Answers Best Answer: If you want to reverse the name in the same cell, without ... Excel Switch First Last Name Order Software - SobolsoftThis software offers a solution for users who want to change "<First> <Last>" to "<Last>, <First>" or "<Last>, <First>" to "<First> <Last>" in a block of MS Excel cells. Excel how to switch names from Last, First to First LastKevin, You could use the Data > Text to Columns wizard to break your column of data into 2 columns, a column with last name and then a column with first name. 8/1/2012 9:52:54 AM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 