Results 1 to 6 of 6

Thread: suggestions

  1. #1
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Question Unanswered: suggestions

    I have a table that contains a column with the last Name, First name in one column. How do I separate the two out so that I might create a table that has two columns one with just the last names and the others with just the first names?

    Any help you can give would be appreciated.

    Thanks,

    Carley
    CAT :-D

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    If the name is always in the format of Last Name plus a comma and a space and then First Name, this will work for you. Create a query in Design View and don't add any tables and switch to SQL view and paste the following in, replacing the table and field names with your own.

    SELECT Left([Table with Full Name]![Last with First],InStr([Table with Full Name]![Last with First],",")-1) AS [Last Name], Right([Table with Full Name]![Last with First],Len([Table with Full Name]![Last with First])-InStr([Table with Full Name]![Last with First],",")-1) AS [First Name] INTO [New Table w 2 Columns]
    FROM [Table with Full Name];

    TD

  3. #3
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    It is in the format of Last name space First name. So example would be
    "Smith Carol"
    CAT :-D

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And how do you suppose to handle the name of el Rio Jack??? Or for that matter any other multi-part last name ... Ah but wait, there is an answer (actually multiple answers): Try running a routine using the Split function or you could use the InStr function ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    Ok mighty Pooh bah how do I run a routine using split? I am game to learn. Also love to learn new things.
    CAT :-D

  6. #6
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    This will do the trick without the comma.

    SELECT Left([Table with Full Name]![Last with First],InStr([Table with Full Name]![Last with First]," ")-1) AS [Last Name], Right([Table with Full Name]![Last with First],Len([Table with Full Name]![Last with First])-InStr([Table with Full Name]![Last with First]," ")) AS [First Name] INTO [New Table w 2 Columns]
    FROM [Table with Full Name];

    TD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •