In my property owner databases I get my first name and last name joined.
ALFORD CARL L
BOOTH MICHAEL L & ANGELA D (if theres shared, or 2 owners)
PIERCE COMER C JR
Because all the names are not set up in the same way, (initials, 2 owners, etc.) Because of this reason, when i want to seperate them i have to put commas wherever i want each record to be seperated, then export it into text and then import it back and make comma the seperator.
is there any set standards to the split i.e. on instance of an Apersand (&) split to 2 seperate fields if thats what you want you could write an update query based on the Mid Function
otherwise it could be a bit of a nightmare and probably impossible without user intervention
Edit code added
SET tblName.col1 = Trim(Mid([Col1],1,InStr(1,[Col1],"&")-1)),
tblName.Col2 = Trim(Mid([Col1],InStr(1,[Col1],"&")+1,(Len([Col1])-InStr(1,[Col1],"&")+1)))
WHERE (((tblName.col1) Like "*&*"));