Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query to separate names (2000)

    Hi!

    I'm working on a database where the user has been entering names as first name/ last name all in one field vs separating them out. They now need to have the names separated. I would like to use a query to do this however am not sure what to put in. I know how to join twofields as one but can't do the reverse. The field as it stands now is [EmployeeName] and the format is first name last name or [Jane Doe]. I need a field for [LastName] and a field for [FirstName]. I do not want to disrupt or change the original infomation.

    Thanks!

    Leesha

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    New York, New York
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to separate names (2000)

    check out the thread beginning in <post#=302777>post 302777</post#> . You can either use this in a select query or you can use it in an update query to write the data back to two separate newly created columns in the table.

    best,
    Karl

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to separate names (2000)

    Hi Karl,

    I tried Han's first approach with the query as this is what I was looking for but got nothing in the columns. I did not get any error messages as in parameter values when I substituted my field name [Recommended Therapist] for the the one in the example. This is my formula:

    FirstName: Left([Recommended Therapist],InStr([Recommended Therapist]," ")-1)
    and
    LastName: Mid([Recommended Therapist],InStr([Recommended Therapist]," ")+1)

    Any ideas on why neither column is returning a value?

    Thanks,
    Leesha

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query to separate names (2000)

    You might try putting the InStr as a separate expression in a column to the left of the First and LastName columns so you can see what is being returned - it may be that you have spaces that preceed the combined Name field - you may need an LTrim function if that is the case.

    FYI, the followon post by <!profile=SteveH>SteveH<!/profile> suggesting you use Excel is what I use for most situations like this. Names aren't terribly regular, so you get some with middle names or intials, and you also have Last Names that have spaces such as Van Meiter, and de Lahoya. Excel works well in terms of letting you see the irregular ones and correct them, and then reimport the data in a new table.
    Wendell

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to separate names (2000)

    Oh God Wendell, you give me more credit than I deserve to be able to follow what you suggested. Would you mind posting an example?

    Re the Excel suggestion, that would be my first choice, however the names need to go into merge fields in reports and the end user wants the simplest means possible and exporting to and using excel would not be there choice.

    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to separate names (2000)

    Create a column in your query:

    <code>PosOfSpace: InStr([Recommended Therapist]," ")</code>

    What is the result if you switch to datasheet view? If this column displays zeros, the character between the first and last name is not an ordinary space.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to separate names (2000)

    Hi Hans,

    It displays a single numbers, no zero's, no names. I'm not sure what the number corresponds to.

    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to separate names (2000)

    That's fine. This column displays the position of the space in the recommended therapist field. For example, in "Will Hoffman" the space comes after 4 letters, i.e. the space is in the 5th position. Other examples:

    <table border=1><td>Jim Jones</td><td align=right>4</td><td>Edna Evans</td><td align=right>5</td><td>Caroline Tenson</td><td align=right>9</td></table>
    Now add this column to the right of the PosOfSpace column:

    <code>FirstPart: Left([Recommended Therapist],[PosOfSpace]-1)</code>

    Does this display anything?

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to separate names (2000)

    Yes! Now it gives me the first name!!! Now all I need is the last name. God I appreciate this!

    Leesha

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to separate names (2000)

    Great! Now the next step: add a new column to the right of the ones you have:

    <code>SecondPart: Mid([Recommended Therapist],[PosOfSpace]+1)</code>

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to separate names (2000)

    THANK YOU!! Worked beautifully. Now to go back and study it so I can understand it and duplicate it down the line.

    Leesha

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query to separate names (2000)

    It looks like Hans has solved your problem, and he did pretty much as I suggested.

    However, you should understand the Excel alternative, as it has a number of benefits with irregular names - if you get more than say, 10,000 names, you definitely need the programatic approach, but for smaller numbers of names, I actually find Excel to be quicker and more accurate. The steps are as follows:<UL><LI>Copy the table, or at least the primary key and the name fields and paste them into an Excel workbook,<LI>Use the Data / Text to Columns to break the name into columns,<LI>correct the obvious errors by recombining Last Names that contain spaces, or suffixes such as Jr. or III, and<LI>Import the resulting data back into Access or link to it and run an update query to populate the new FirstName and LastName fields.[/list]Hope this is a bit clearer. We routinely have about 6 fields that we use for databases; First, Middle, Last, Prefix, Suffix and AKA-Nickname. In some cases we also use a special mailing label field where an individual wants some variation of his name used on mailings.
    Wendell

Posting Permissions

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