How to split data in one field into two fields?

Windows XP, Access 2000
========================
Hi,

I have a table with a field for firstName and another for
lastName. Unfortunately, the suffixes for the person (eg.
Jr., II, RN, RD, PhD etc.) were entered along with the
last name. So, the lastName has data values like "Roberts,
RN" or "Clinton Jr.". I'd like to create another field and
then split the data to keep just the last name in the
lastName field, and the rest in the Suffix field.

Desired:
---------
lastName | Suffix
====================
"Roberts" | "RN"
"Clinton" | "Jr."

I've looked at the string functions in the help section,
but didn't find any function that would help me accomplish
this. I guess I'm looking for something similar to
the "Data--Convert Text to Columns" in MS Excel.

I could export the data from Access to Excel, split it,
and then import it back. But would prefer to do this
without importing/exporting data.

Thanks for any help or pointers regarding this.

-Amit

Unfortunately, the suffixes for the person (eg.
Jr., II, RN, RD, PhD etc.) were entered along with the
last name.

Hi Amit,

There is no magic way to resolve this. My approach (after tracking down the original designer and asking him to spend his next vacation fixing it) would be to get methodical.

1. Add new columns to the table for anything missing such as Title (Mr., Sra...), MiddleInitial, Suffix (Jr., III ...)

2. Make a list of all the likely bits you are looking for (" Jr.", " Jr", " Junior", etc). Don't forget likely typos like "Juniro" and hope it's not part of a real name...

3. For each probable suspect, get a result set
"Select LastName From Customers Where LastName Like '* Jr.';"

4. [Optional] Review the results to see if you got anything that looks like it doesn't fit. If you find anything, refine your search criteria.

5. Write some code that goes through the result and, using Mid() and Instr(), parse the name and save each part to separate variables. Then write them back to the correct columns.

It's unlikely that it will be perfect when you are finished.

Hope that helps

Jay

-----Original Message-----
Unfortunately, the suffixes for the person (eg.
Jr., II, RN, RD, PhD etc.) were entered along with the
last name.

Hi Amit,

There is no magic way to resolve this. My approach (after
tracking down the original designer and asking him to
spend his next vacation fixing it) would be to get
methodical.

1. Add new columns to the table for anything missing such
as Title (Mr., Sra...), MiddleInitial, Suffix (Jr.,
III ...)

2. Make a list of all the likely bits you are looking for
(" Jr.", " Jr", " Junior", etc). Don't forget likely typos
like "Juniro" and hope it's not part of a real name...

3. For each probable suspect, get a result set
"Select LastName From Customers Where LastName Like '*
Jr.';"

4. [Optional] Review the results to see if you got
anything that looks like it doesn't fit. If you find
anything, refine your search criteria.

5. Write some code that goes through the result and,
using Mid() and Instr(), parse the name and save each part
to separate variables. Then write them back to the correct
columns.

It's unlikely that it will be perfect when you are
finished.

Hope that helps

Jay

Hi Jay,

Thanks for your response. It *is* definitely helpful. Just
need to be more careful when designing databases in future.

-Amit

-----Original Message-----
I have a table with a field for firstName and another
for lastName. I'd like to create another field and
then split the data to keep just the last name in the
lastName field, and the rest in the Suffix field.
-Amit

Take a look at "Splitter for Microsoft Access"
Splits names in Access tables, including suffixes!
http://www.infoplan.com.au/splitter

- Mark K.

This might be unhelpful but I had to do the reverse of this once (ie combine two columns into one) and there may be an opposite method:

1. make a copy of the database to work from
2. then transfer the relevant columns (firstname and lastname in my particular case) into Excel and then use the concatenate formula
3. This gave me the data in one column (double check by skimming the column)
4. Paste the column back into the table.

There is probably the reverse of concatenation in excel although I have never used it.

Good luck!

"Mark King" wrote:

-----Original Message-----
I have a table with a field for firstName and another
for lastName. I'd like to create another field and
then split the data to keep just the last name in the
lastName field, and the rest in the Suffix field.
-Amit

Take a look at "Splitter for Microsoft Access"
Splits names in Access tables, including suffixes!
http://www.infoplan.com.au/splitter

- Mark K.

It looked interesting. So, I thought I'd try out solving this. In Excel, you
can use the SEARCH/REPLACE combination. But, this was possibly a little more
clean.

SELECT tblVendors.VendorName,
IIf(InStr([vendorname],",")0,Left([vendorname],InStr([vendorname],",")-1))
AS [first],
IIf(InStr([vendorname],",")0,Trim(Mid([vendorname],InStr([vendorname],",")+1))) AS [Last]
FROM tblVendors;

This is a simple select query, with tblVendors being the table in question,
and VendorName being the field to split up.
I would create two extra fields and change this query to an update query
when you feel comfortable with it. You will also want to go through the code
and note any inconsitencies. This also assumes that you are merely looking
for a comma.

Hope it helps...if you get this.

BC

"Amit" wrote:

Windows XP, Access 2000
========================
Hi,

I have a table with a field for firstName and another for
lastName. Unfortunately, the suffixes for the person (eg.
Jr., II, RN, RD, PhD etc.) were entered along with the
last name. So, the lastName has data values like "Roberts,
RN" or "Clinton Jr.". I'd like to create another field and
then split the data to keep just the last name in the
lastName field, and the rest in the Suffix field.

Desired:
---------
lastName | Suffix
====================
"Roberts" | "RN"
"Clinton" | "Jr."

I've looked at the string functions in the help section,
but didn't find any function that would help me accomplish
this. I guess I'm looking for something similar to
the "Data--Convert Text to Columns" in MS Excel.

I could export the data from Access to Excel, split it,
and then import it back. But would prefer to do this
without importing/exporting data.

Thanks for any help or pointers regarding this.

-Amit

Looks like you need to search for spaces (not commas) in the lastname field,
since one of the two examples you gave did not have a comma. You can use
this expression to parse out the suffixes in a query:

Trim(Mid(LastName, InStr(LastName, " ") + 1))

You can test it in a SELECT query:

SELECT Trim(Mid([LastName],InStr([LastName]," ")+1)) AS SuffixTest
FROM tblVendors;

and if the results look right to you, then change the query to an update
query and run it.

UPDATE tblVendors SET Suffix = Trim(Mid([LastName],InStr([LastName],"
")+1));

You will need to have already added the Suffix field to your table to run
the update query.

HTH
Paul

"BC-w2k3s" wrote in message
...
It looked interesting. So, I thought I'd try out solving this. In Excel,
you
can use the SEARCH/REPLACE combination. But, this was possibly a little
more
clean.

SELECT tblVendors.VendorName,

IIf(InStr([vendorname],",")0,Left([vendorname],InStr([vendorname],",")-1))
AS [first],

IIf(InStr([vendorname],",")0,Trim(Mid([vendorname],InStr([vendorname],",")
+1))) AS [Last]
FROM tblVendors;

This is a simple select query, with tblVendors being the table in
question,
and VendorName being the field to split up.
I would create two extra fields and change this query to an update query
when you feel comfortable with it. You will also want to go through the
code
and note any inconsitencies. This also assumes that you are merely looking
for a comma.

Hope it helps...if you get this.

BC

"Amit" wrote:

Windows XP, Access 2000
========================
Hi,

I have a table with a field for firstName and another for
lastName. Unfortunately, the suffixes for the person (eg.
Jr., II, RN, RD, PhD etc.) were entered along with the
last name. So, the lastName has data values like "Roberts,
RN" or "Clinton Jr.". I'd like to create another field and
then split the data to keep just the last name in the
lastName field, and the rest in the Suffix field.

Desired:
---------
lastName | Suffix
====================
"Roberts" | "RN"
"Clinton" | "Jr."

I've looked at the string functions in the help section,
but didn't find any function that would help me accomplish
this. I guess I'm looking for something similar to
the "Data--Convert Text to Columns" in MS Excel.

I could export the data from Access to Excel, split it,
and then import it back. But would prefer to do this
without importing/exporting data.

Thanks for any help or pointers regarding this.

-Amit