split fields

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi

I have adopted a database that was created from a Paradox import.

The Contact table has 4000+ records. Unfortunately, most of the names are
input as "Smith, Jim" or "Smith, Jim and Mary" all in one field called
Contact Name.

What is the easiest way to split the name information up so that I have four
fields of data: strLastNamePri, strFirstNamePri, strLastNameSec,
strFirstNameSec?

All positive input is appreciated.
 
CJ

First, if you want to be able to use the strong features and functions of
Access, you don't want to structure your data as you described. While
having LName, FName, 2ndLName, 2FName might be how you'd do it in Excel or
another spreadsheet, Access is a relational database.

If the topics of normalization and relational design are unfamiliar, brush
up on them before using Access to do this. If you treat it as a
spreadsheet, you'd be better off using a spreadsheet! (no problem with
that, just pushing to use the tool you need for the job...)

If your data truely is as variable as you described, I don't believe Access
is smart enough to parse it without help. I've found it particularly useful
to develop the queries that can give Access a start, but then rely on USB to
finish the job ("use someone's brain!").

It may be that the original data is very consistent, and you'll be able to
create a series of queries that will handle parsing the names completely ...
and it may be the first time that's ever happened! <g>

Take a look at the Left(), Right() and Mid() functions, as well as the
InStr() function.
 
Thanks Jeff, I didn't think there was an easy way to break the data apart.

One question though. What's wrong with having a primary contact name and a
secondary contact name for each contact record? The contact names are not
going to change, I'm not going to be adding ChildOne, ChildTwo etc and you
need to have names and phone numbers for each account.

Taking normalization into account, I know that every database can still be
designed in more than one way but a separate table for contact name seems
like a bit of overkill to me.
 
Hey CJ

I split data from column often. Use the Convert Text to Column Wizard built
in Excel. Use The Fixed With. It is the easiest way. Please let me know if
you need further assistance.

MRZ
 
Good going CJ....
Hey you might taking a look at my posting? COMPARING TWO QUERIES.... I will
appreciate your input.

Thanks,
MRZ
 
CJ

Always tough to decide where to draw the line <g>.

An advantage to having a single table of folk is you can easily find
(possible) duplications. If you have two separate sets of "person name"
fields, you get to build UNION queries to find all possible names.

It might be overkill in your situation. Were it mine, I'd probably use a
tblPerson to record person-related info, including name. Then I'd use two
contact person fields in tblContact, but record the IDs from tblPerson.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top