append 1-->many

[gchien30]

Hi, I am trying to append names from one chart to another. However, the first chart, the name is stored in one field, and in the second chart, there is a first and a last name field. Is there anyway to split up the names so everything before the "space" goes in First and after goes in Last? Thanks!

This is what I could come up with so far to append the actual data...

INSERT INTO tblPrograms (Contact_First, Contact_Last, Contact_MI)
SELECT 3 AS Org_ID, Programs.[ContactName] AS Expr1 FROM Programs;

 

[fuzzygeek]

Split

Study the following, it breaks a last name separated by a first name by a space into the two components.

Function TestSplitField()
Dim DName As String
Dim FName As String
Dim LName As String
Dim L As Long
Dim Lgth As Long

DName = "Bodkins Odds"
Lgth = Len(DName)
L = InStr(1, DName, " ", vbTextCompare)
LName = Left(DName, L - 1)
FName = Right(DName, Lgth - L)
Debug.Print "L - " & LName
Debug.Print "F - " & FName
End Function

 

[jeremie_ingram]

Use the query builder and add these two lines in the top line to create a calculated field.

LName: Left([NAME],(InStr([Name],","))-1)

And

FName: Right([NAME],((Len([Name])-(InStr([Name],","))-1)))

This is on a field containing the first and last name in the format of Jones, John.

What you need to do is determine the delimiter (n this example it is the common, but if your names appear as John Jones then it would be a space). Next you determine the number of characters to the delimiter with the InStr function. InStr([Name],",")
Now that you know how far it is to the delimiter, you can use the LEFT function to return all text to the left of the delimiter (-1 is added to remove the delimiter itself).
The RIGHT function performs in the same manner, but you take the total length Len of the field and subtract the distance to the delimiter -1. This will return everything to the right of the delimiter.
The field [NAME] in my example would contain Jones, John. The two fields in the query would return the names separated into the appropriate column. If your filed has John Jones, replace the "," in the equation with " " and reverse the names of the fields (FName to LName).

Using an append query, you could transfer all of the data from one table to another and separate the names. Add the NAME filed to the query, but done append it to any field in the new table.