Prev: Maybe Update Query?
Next: Total by certain time period
From: Iram on 3 Jun 2010 19:35
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
From: John W. Vinson on 3 Jun 2010 20:38
On Thu, 3 Jun 2010 16:35:15 -0700, Iram <Iram(a)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]
From: Iram on 4 Jun 2010 11:22
Thank you John W. Vinson!



"John W. Vinson" wrote:

> On Thu, 3 Jun 2010 16:35:15 -0700, Iram <Iram(a)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]
> .
>
 | 
Pages: 1
Prev: Maybe Update Query?
Next: Total by certain time period