Split two columns into four

[Nick X]

Hi all,
If it were only that easy. Here's what I have:

NameField1 | SpouseField2
lname,fname mname | null
lname,fname mname and fname2 mname | null
lname,fname mname | lname2,fname2 mname2

NameField1 contains name and sapouses name if lname is the same. No spaces
at comma.
This is what the final product needs to look like:
LName1Field | LName2Field | FName1Field | FName2Field (with middle initials
with FName)

Any input on this would be greatly appreciated,
Nick

 

[Lord Kelvan]

SELECT IIf(InStr([namefield1],",")-1 Is
Null,"",Left([namefield1],InStr([namefield1],",")-1)) AS LName1Field,
IIf(InStr([SpouseField2],",")-1 Is
Null,"",Left([SpouseField2],InStr([SpouseField2],",")-1)) AS
LName2Field,
IIf(InStr([namefield1],",")-1 Is
Null,"",Mid([namefield1],InStr([namefield1],",")+1)) AS FName1Field,
IIf(InStr([SpouseField2],",")-1 Is
Null,"",Mid([SpouseField2],InStr([SpouseField2],",")+1)) AS
FName2Field
FROM mytable;


based on the format of

NameField1
lname,fname midname

SpouseField2
lname,fname midname

no use fo the word null or | as you used as for initials if the mid
name had full naems to start with and not initials because a person
can have as many middle names as there are colours. i for instance
have 2 soem people have 1 some people i know have 4 or 5 paste soem
actual sample data if the above query dosnt work and then manually
format it how you want it to be

Regards
Kelvan