Results 1 to 4 of 4

Thread: Parsing (97)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing (97)

    I have a table that is not normalized. I want to separate the name field so that first name, last name and if there is a title - are in separate columns. Is it possible to use an update query to do this? How would I set it up? Any help is appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parsing (97)

    is there any standard to the way data exists now in your table? In other words, Do you have things like "Dr. John Watson, MD" AND "John Watson MD" and "Dr & Mrs John Watson"? If you have a mixture like that, you would have to do it in multiple passes with an update query unless you created a function to parse the names out and return the specific parts to your query.

    There is code in the sample database NeatCd97.mdb for parsing names. You can download NeatCd97 here.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing (97)

    Is there any way to use MakeTable query and take the name field and separate it? I am not that good at creating code or adapting it to what I need so specifics would be appreciated. To simplify things (if that is possible) I think there is only a first and last name in the name field.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parsing (97)

    Sure, if all you have is a first and last name in the field. Create a maketable query and instead of using the name field, create two expressions like this:

    FirstName: Left$([name],instr([name]," ")-1)

    LastName: Mid$([name],instr([name]," ") +1)

    That should parse out the first and last name into separate fields, assuming that the name is formatted firstname <space> lastname.
    Charlotte

Posting Permissions

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