Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Normalizing an Existing Database (Access 2000 SP1)

    I have inherited a database that is not normalized, the name field is in the format "LastName, FirstName" is there anyway programatically that can split this field into 2 separate fields, using the comma as the splitting point? Thanks shihalud

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalizing an Existing Database (Access 2000 SP1)

    You can use this technique on the Access Web: http://www.mvps.org/access/strings/str0001.htm

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

    Re: Normalizing an Existing Database (Access 2000 SP1)

    In addition to Preston's suggestion, if you don't have literally thousands of names, I frequently use the Excel command Date / Text to Columns as it is very visual, and if you have names with prefixes, suffixes and other such things, you can simply highlight, click and drag to correct the names that don't come through cleanly with a parse.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalizing an Existing Database (Access 2000 SP1)

    Thanks Preston for the source. I went to the link and used the two functions to split up my Name field into two separate fields. However I did not think the problem all the way thru, some records contain Suffix, MI, two first names, etc, as suggested by Wendell. I also looked at the "Parsing character separated string into individual components" on the same site. Will the code listed separate the proper components of each record? I modified one of the first two functions:
    MI: Right$([FirstName],Len([FirstName])-InStr(1,[FirstName]," "))
    this returned a MI if there was one, but if not it returned the FirstName instead of nothing. Wendell's suggestion would not work because as he mentioned if there are too many records ( this DB has 8k - 9k records). Any ideas? thanks shihalud

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

    Re: Normalizing an Existing Database (Access 2000 SP1)

    This situation is hard to handle completely automatically. You will probably have to write code using DAO or ADO to loop through the records and parse the names, and then write the parts to different fields. I have attached some code that originates in the Neatcd97.mdb sample database provided by Microsoft (<!mskb=177972>Microsoft Knowledge Base Article 177972<!/mskb>). The code will split a name into its constituent parts.

    But even after using this function, you will still have to inspect records manually/visually. Some names will almost certainly have been entered inconsistently, throwing off automated parsing methods.
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalizing an Existing Database (Access 2000 SP1)

    Thanks for everyones help, the last bit of info is a major step in the right direction. Thanks I should be able to parse the majority of the records and the few left will be a far cry from the mass of names I started out with in this project. Shihalud

Posting Permissions

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