Splitting a field with information inside?

[sarajini]

Posted in utteraccess

Hi,
I plan to import an Excel spreadsheet that is not normalized.
The Excel sheet has a column for Advocates. A client can have either one or two advocates. If a client has two advocates, they are both listed in the same column. The names are separated by a slash, like this:

Advocate(s):
Smith
Jones
Smith/Jones
Smith/Roberts

How should I get Access to split up the Advocate field into an Advocate1 field and an Advocate2 field? (I’ve seen some simple firstname/lastname split code. But mine has a little complication: If there is no second Advocate, I’d just like the field to stay blank.)

Thanks,
sarajini

 

[Cosmos75]

In your excel spreadsheet use the next two colums and give them a header Adovocate1 and Advocate2.

In the column Advocate1, use this formula (assuming your text is in Cell A2).
=LEFT(A2,SEARCH("/",A2)-1)

In the column Advocate2, use this formula (again, assuming your text is in Cell A2).
=RIGHT(A2,LEN(A2)-SEARCH("/",A2))

You columns from left to right should look like this
Smith/Jones, Smith, Jones

You should then be able to import the two new columns (Adovocate1 and Advocate2) into an Access table.

Hope this helps?


This will work as long as as nobody has a name with a "/" in it (probably won't be the case, will it?).

 

[Cosmos75]

(I’ve seen some simple firstname/lastname split code. But mine has a little complication: If there is no second Advocate, I’d just like the field to stay blank.)

Sorry missed that!

Change the formula from Advocate1 to
=IF(ISERROR(LEFT(A3,SEARCH("/",A3)-1)),A3,LEFT(A2,SEARCH("/",A2)-1) )

Change the formula from Advocate2 to
=IF(ISERROR(RIGHT(A3,LEN(A3)-SEARCH("/",A3))),"",RIGHT(A3,LEN(A3)-SEARCH("/",A3)))

 

[sarajini]

Hi Cosmos!
Thanks for your help.
I actually decided to combine Advocate1 and Advocate2 columns, into one "advocate" table once the info gets into access. (It's to follow those normalization rules.)

What would be the best way: separate the advocate columns while they're in access, or while they're still in excel?

thanks!!!!
sarajini

 

[Cosmos75]

is it ALWAYS either going to be one or two advocates? You could have a seperate field then for each advocate in your access table. Have two fields (Advocate1 and Advocate1) be lookup fields to an advocate in and advocate table.

BUT (to be normalized), if there could come a time when there could be more than two that it might be better to create a many to many ralationship.

Many (person) to Many (Advocates).

tblPerson
PersonID (Primary Key)
Person

tblAdvocate
AdvocateID (Primary Key)
AdvocateName

tblJunction
PersonID (Foreign Key)
AdvocateID (Foreign Key)

 

[sarajini]

Hi Cosmos,
Yes, we only have one or two for each case. (90% of the time, it's just one advocate.) So I don't think we need to create a table with the many-to-many relationship.
Should I start trying to separate them once they're in Access? would it be best to try an sql statement, or work on the QBE grid?

thanks....
sarajini

 

[Cosmos75]

Are you going to store each advocate in an table for advocates? Is the last name enough to uniquely identify the advocate? (John Smith vs Joe Smith)

 

[Mile-O]

Yes, we only have one or two for each case. (90% of the time, it's just one advocate.) So I don't think we need to create a table with the many-to-many relationship.

Best to err on the side of caution and have a junction table between case and advocates?

 

[sarajini]

Hi Cosmos and Mile!
Let's see.
I am going to store each advocate in a table for advocates. We have about 40 advocate names. There's actually no duplication of advocate lastnames (if we were to get both a Joe Schmo and a Rob Schmo, we'd just add the initial to each name.) But the advocate table does have an autonumber primary key.

Each advocate has many cases, and each case can have 1-2 advocates.

Hmm...I was planning to import the database from excel, then separate out a table stating the CaseNumber and the AdvocateNames for each case.

Wouldn't that work as a junction table? I could add the Advocate autonumber primary key to the junction table. Or I just make the lastname the primary key, and make a junction table of lastname and casenumber. Which sounds like the best option?

So, when I import, how should I ask access to separate the advocatenames, and then make a list of advocates and casenumbers?

thanks so much...
sarajini