Split data from column

[jomuir]

I am trying to create an update query ....this is to split up data in a column into 1-5 columns.

This is a Title, FirstName & Surname column into Title1, Title2, Title3, Title4, FirstName1, FirstName2, FirstName3, FirstName4......

The initial fields are populated like:- Name, Name & Name, Name & Name & Name, Name & Name & Name & Name

So far I have:-

FirstName1 update to:- Trim(IIf([FirstName] Like "*&*",Left([FirstName],InStrRev([FirstName]," &")),[ FirstName]))

FirstName2 update to:- IIf([FirstName] Is Null,Null,IIf([FirstName] Like "* & *",Trim(Right([FirstName],Len([FirstName])-InStr([FirstName],"& "))),IIf([FirstName] Like "* & *",[ FirstName],Null)))

I am lost

 

[Kafrin]

First I'd highly recommend bringing all your data into the same format, so you don't have both commas and ampersands to worry about.

To do this, I suggest you run an update query on your existing data:

UPDATE Table1 SET Table1.Field1 = Replace([Field1],","," &");

This will replace any commas in your data with space+&, so you're always looking for the same thing in your strings.

As for the rest of your problem, I think we need some more information. Are you coming from 3 columns (Title, FirstName, Surname) or from one column (Title FirstName Surname all in one field)? Does every name always have all three parts of title/first name/surname?

 

[jomuir]

Sorry, I did not explain that well....there are no coma's just &

So it could be like this (examples)

TITLE FIELD
MR
MR & MS
MR & MRS
MR & MRS & MR
MR & MRS & MR & MRS
MR & MR & MR & MR & MR

FIRSTNAME
John
John & Rachel
John & Rachel & David
John & Rachel & David & Craig
J & R & D & C & P

LASTNAME
SMITH
SMITH & SMITH
SMITH & SMITH & MURRAY
SMITH & SMITH & MURRAY & BLACK
SMITH & WHITE & MURRAY & BLACK & STEWART

So the last one would be....split into 15 fields, 5 title, 5 firstname & 5 lastname....looking like this:-

Mr J Smith MR R White Mr D Murray Mr C Black Mr P Stewart

 

[Kryst51]

This looks very unnormalized. Is this a first step in normalizing, or is this the final out put of what you are looking for?

 

[jomuir]

Final output - It was used as a mailing list but have to be split into this format for an external company to upload into the main database. So these are going to be my headers for the names.....

"Title_1"|"FirstName_1"|"LastName_1"|"Title_2"|"FirstName_2"|"LastName_2"|"Title_3"|"FirstName_3"|"LastName_3"|"Title_4"|"FirstName_4"|"LastName_4"

 

[Kafrin]

FirstName1: Trim(Iif(InStr(FirstName," & ")<>0, Left(FirstName,InStr(FirstName," & ")),FirstName))

...

Do you need to do this as an update query, or can you use code instead?

 

[jomuir]

Which ever is easier, I was just doing it as an update query as I find that easy to use and test.....however, I am happy to try it either way.

 

[Kafrin]

OK, in code there's a great little function called Split which returns an array (so it can't be used in queries as far as I'm aware). This will split your string into multiple strings, eg:

Dim rs as Recordset, x As Variant, ctr As Byte
Set rs = [your table]
 
rs.MoveFirst
Do Until rs.EOF
  x = Split(rs!Title," & ")
 
  For ctr = 0 to x.UBound
    rs.Edit
    rs("Title" & (ctr+1)) = x(ctr)
    rs.Update
  Next
 
  rs.MoveNext
Loop

Obviously repeat for the FirstName and Surname fields.