Separating People

[Brian]

I have been given a new requirement for a database that I have been using
for a long time. I have a Name field that needs to show as FirstName and
LastName for one report. I am having a hard time figuring out how to modify
the query so that it only calls up the first or last names. I can see how to
select a certain character length, but until we can convince everyone to
have names of the same length, I can't see that helping. The names are all
stored with first name, a space, then last name.

Any help would be appreciated.

Thanks,

Brian

 

[Rick B]

Well, you have discovered why you should always store names in separate
fields

You can modify your table to include a new "FirstName and LastName field,
then you can build an update query to populate them. Then, you can delete
your original field. Your datbase will then have a more normalized
structure and you will be able to display the names in several possible
formats.

You can read the previous posts on this topic, and you will find ways to
separate the names as you stated by pulling everything up to the space to
one field, and everything after the space to another field, but here's the
problem. What happens to "Mary Alice Smith"? What about "J. R. Ewing"?
What about "John Smith Jr."? You will most likely end up with exceptions.

The common suggestion is to go ahead and run your update query, but then you
will need a human to go back through and fix all the exceptions.

Hope that helps,

 

[Rick B]

One more example "Carla de los Sotos"


By the way, here is a previous post that answers how to do this. It is from
"Sprinks" who posts some great responses in the groups!

--------------------------------------

If they are separated by a space, as in "John Smith", add two new fields to
your table, e.g., FName and LName, and use an update query to parse the name
into the two fields, using the Left, Right, and InStr functions. For an
initial field named "StudentName", the SQL is:

UPDATE Students SET Students.FName =
Left([StudentName],InStr([StudentName]," ")-1), Students.LName =
Right([StudentName],Len([StudentName])-InStr([StudentName]," "));

See VBA Help for more information on the string functions.

Hope that helps.
Sprinks

 

[Rick B]

Note that I would probably use the "Left" statement below, but I would not
use the "right" statement. The reason is that it would lose data.

Maria de los Sotos would become Maria (First) and Sotos (Last). You'd
lose all the middle.

I'd use a "MID" statement to grab everything after the first space and store
that as my last name. It will still be wrong sometimes (Mary Alice Smith)
but at least it won't miss data.

--
Rick B

One more example "Carla de los Sotos"


By the way, here is a previous post that answers how to do this. It is
from "Sprinks" who posts some great responses in the groups!

--------------------------------------

If they are separated by a space, as in "John Smith", add two new fields
to
your table, e.g., FName and LName, and use an update query to parse the
name
into the two fields, using the Left, Right, and InStr functions. For an
initial field named "StudentName", the SQL is:

UPDATE Students SET Students.FName =
Left([StudentName],InStr([StudentName]," ")-1), Students.LName =
Right([StudentName],Len([StudentName])-InStr([StudentName]," "));

See VBA Help for more information on the string functions.

Hope that helps.
Sprinks

I have been given a new requirement for a database that I have been using
for a long time. I have a Name field that needs to show as FirstName and
LastName for one report. I am having a hard time figuring out how to
modify the query so that it only calls up the first or last names. I can
see how to select a certain character length, but until we can convince
everyone to have names of the same length, I can't see that helping. The
names are all stored with first name, a space, then last name.

Any help would be appreciated.

Thanks,

Brian

 

[Brian]

Well, you have discovered why you should always store names in separate
fields

Where were you when I started this database five years ago?! Now if I
make that chage it will affect a couple dozen queries and reports that would
all need to be changed!

You can read the previous posts on this topic, and you will find ways to
separate the names as you stated by pulling everything up to the space to
one field, and everything after the space to another field,

I was looking for this, but couldn't find a reference. I searched for
'Update' in here and only found four references. Oh well. I guess you had it
downloaded before the server got rid of it, or maybe the news server I use
clears them out more frequently. Thanks for finding it for me. It worked
perfectly!

but here's the problem. What happens to "Mary Alice Smith"? What about
"J. R. Ewing"? What about "John Smith Jr."? You will most likely end up
with exceptions.

I don't care about those folks, I will just delete them! (OK, maybe not <sly
grin>) Turns out there weren't many, so no problem.

The common suggestion is to go ahead and run your update query, but then
you will need a human to go back through and fix all the exceptions.

Hope that helps,

It did! Thanks again!

 

[Brian]

Doh! And I thought I was done! Thats a good idea, I will change it that way.

Thanks, yet again,

Brian

Note that I would probably use the "Left" statement below, but I would not
use the "right" statement. The reason is that it would lose data.

Maria de los Sotos would become Maria (First) and Sotos (Last).
You'd lose all the middle.

I'd use a "MID" statement to grab everything after the first space and
store that as my last name. It will still be wrong sometimes (Mary Alice
Smith) but at least it won't miss data.

--
Rick B

One more example "Carla de los Sotos"


By the way, here is a previous post that answers how to do this. It is
from "Sprinks" who posts some great responses in the groups!

--------------------------------------

If they are separated by a space, as in "John Smith", add two new fields
to
your table, e.g., FName and LName, and use an update query to parse the
name
into the two fields, using the Left, Right, and InStr functions. For an
initial field named "StudentName", the SQL is:

UPDATE Students SET Students.FName =
Left([StudentName],InStr([StudentName]," ")-1), Students.LName =
Right([StudentName],Len([StudentName])-InStr([StudentName]," "));

See VBA Help for more information on the string functions.

Hope that helps.
Sprinks

I have been given a new requirement for a database that I have been using
for a long time. I have a Name field that needs to show as FirstName and
LastName for one report. I am having a hard time figuring out how to
modify the query so that it only calls up the first or last names. I can
see how to select a certain character length, but until we can convince
everyone to have names of the same length, I can't see that helping. The
names are all stored with first name, a space, then last name.

Any help would be appreciated.

Thanks,

Brian

 

[Guest]

I have a similar situation, but I need the Middle Name separated into its own
field. I have the separate name fields working, but need to remove the
middle name from the first name field they are both in the first name field
together.
My names are formatted like... Doe, John L or Smith, Sally Ann or Jones, Randy
This is what is in the first name field:
Right(Trim([Name]),Len(Trim([Name]))-InStr(InStr(1,[Name]," ")-1,[Name]," "))
Last Name:
Left$([Name],InStr(1,[Name],",")-1)
Middle Name:
Right(Trim([Name]),Len(Trim([Name]))-InStr(InStr(1,[Name]," ")+1,[Name]," "))

What am I doing wrong?

Doh! And I thought I was done! Thats a good idea, I will change it that way.

Thanks, yet again,

Brian

Note that I would probably use the "Left" statement below, but I would not
use the "right" statement. The reason is that it would lose data.

Maria de los Sotos would become Maria (First) and Sotos (Last).
You'd lose all the middle.

I'd use a "MID" statement to grab everything after the first space and
store that as my last name. It will still be wrong sometimes (Mary Alice
Smith) but at least it won't miss data.

--
Rick B

One more example "Carla de los Sotos"


By the way, here is a previous post that answers how to do this. It is
from "Sprinks" who posts some great responses in the groups!

--------------------------------------

If they are separated by a space, as in "John Smith", add two new fields
to
your table, e.g., FName and LName, and use an update query to parse the
name
into the two fields, using the Left, Right, and InStr functions. For an
initial field named "StudentName", the SQL is:

UPDATE Students SET Students.FName =
Left([StudentName],InStr([StudentName]," ")-1), Students.LName =
Right([StudentName],Len([StudentName])-InStr([StudentName]," "));

See VBA Help for more information on the string functions.

Hope that helps.
Sprinks



I have been given a new requirement for a database that I have been using
for a long time. I have a Name field that needs to show as FirstName and
LastName for one report. I am having a hard time figuring out how to
modify the query so that it only calls up the first or last names. I can
see how to select a certain character length, but until we can convince
everyone to have names of the same length, I can't see that helping. The
names are all stored with first name, a space, then last name.

Any help would be appreciated.

Thanks,

Brian