Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Unanswered: Combining Multiple Columns

    Hello,

    I have a access 2003 database with the columns of "last name" "first name" "middle name" "second middle name".

    i need to combine all of these fields into 1 column called "name" Is there an easy way to do this? thank you for all the help.

    Jeff
    Last edited by idlewild70; 12-04-08 at 17:42.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In a query based on that table, use

    CustomerName: [first name] & " " & [middle name] & " " & [second middle name] & " " & [last name]

    in the Field row of a new column.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2004
    Posts
    29
    I'll give that a try.


    Thanks for the help.

    Jeff

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off don't do it.. you have the individual elements that are required to build the lastname at anytime you need to.

    if you leave the existing columns in place you are creating redundant data, if you delete the existing columns you have lost information. some organisations prefer an address on a letter to be Mr J Idlewild not Mr Jeff Idelwild

    if you store both you have to update both, say someone changes their surname.. say Mr James Smith, decide to become Mz Jocelyn Smythe

    when you extract the data in a query build the name back up using that syntax

    a slight wrinkle on StarTrekker's suggestion

    CustomerName: replace([first name] & " " & [middle name] & " " & [second middle name] & " " & [last name]," "," ")
    ..that replaces any double spaces with a single space.. that way round if you are missing some information you wont have additional spaces creeping in. you will still have a leading space if the title is blank.

    whilst you are at it, consider getting rid of spaces between column names.. its makes future SQL easier to read, and more portable.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2004
    Posts
    29
    Thanks for the help guys. That did exactly what i needed it too.


    Jeff

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Awesome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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