How to split data in one field into two fields?

[Amit]

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

 

[Amit]

-----Original Message-----

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

 

[Mark King]

-----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.

 

[Paul Johnson]

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

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,

AS [first],

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

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