Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: Splitter for Microsoft Access

    Hello. I have a database that has about 1/2 million names in it, all in one column, all different. They have titles, endings (like esq or III), initials, pretty much anything you can think of. I did the first bunch by exporting into Excel and doing Text->Columns since they're delimited by space, but that didn't help when some names are just first and last and some have Mr. & Mrs. First, Middle, Last, Title, so I still had to manually do about 5000, probably, from the first 17,000. Ugh. I wasn't able to find anything that seemed helpful except the above-mentioned splitter, which costs $40. Unfortunately, I can't spend any money, but thankfully, I found you all who will hopefully be able to help me. I hope to help you all in the future, but this is just over my head for now.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Mr and Mrs

    One thing you could try is to create a table (say it's called Replacements) with 1 field which would hold records of stuff like Mr, Mrs, Dr, &, And, etc. You could then write a bit of code which takes your CustomerName field and use the instr function to parse it and match it against the table with all the Mr, Mrs, etc. (i.e. Open the main customer table, open the Replacements table), loop through the Replacements table using the instr function to match it against the customername field. If it found a match with one of the records in the "Replacements" table, it could add the matched criteria to another field in your main table (i.e. Prefix or Suffix) and replace the instr of the customername field with a blank in your main table and then loop through to the next customer record in your main table (hope that makes sense.) You could get a little fancier and have 2 fields in the "Replacements" table, the 1st field would contain the Mr, Mrs, Dr, etc. and the 2nd field could tell it where to put it (i.e. Prefix, Suffix, etc.) It's a bit tricky but I've done it and it works rather well when you utilize the instr function and want to replace wording in a certain field.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2006
    Posts
    3
    Thank you very much for your quick reply. I've not much experience with Access, or any database for that matter, yet, so I'm having issues getting the instr function to work for me quite as you described. I've found an example: Left([CustomerName],InStr([CustomerName]," ")-1) to work, but that just pulls out whatever is on the left of a space and puts it in, for me, my title field. When I try to modify it to search for a specific title, for example: Instr(CustomerName, dr) I receive a bunch of zeros if I place it in my title column -- Title: InStr("FullName_","dr") but absolutely nothing if I place it elsewhere. I have discovered the replace function, which it looks like I will use to remove the titles once I have them moved to the title column, allowing me to continue with the rest of the name.

    I'm sorry for the long post, but I figure I'll put the entirety of my logic behind how I going about this:

    Find title -> move to title column
    Find suffix -> move to suffix column
    First string -> Move to first name
    If "and" or "&" -> add to first name
    Next string -> add to first name
    Else Last string -> move to last name
    Remains -> middle initial

    The deal with the "and" is because sometimes a husband and wife will both be included. On occassion, the husband and/or wife uses a middle initial or name as well, so even if I get this to work, I'll still have to go through by hand and make sure I don't end up with something like George & Chris A. E. Richert where they entered George A. & Chris E. Richert. I would do something based on the length of the string (middle initial) but sometimes I get something like A. George, so that wouldn't work either. Thanks for your help.

    Edit: I hopefully will be able to remove anything after a space in the middle initial column, taking care of the two-initials problem. Ahh... nevermind; we have companies, too, so that would really mess them up.
    Last edited by foreyes4; 08-02-06 at 11:34.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Replacing

    foreyes4,

    Keep in mind that my reply was just one of many solutions. You may yet get some better answers from others on this forum. I used the technique I described because I was faced with a similar delima where I had a table with a 1 field customername field which needed to be broken out and some of the text in the customername field needed to be capitolized (i.e. wps Finanacial needed to be converted to WPS Financial or Wisconsin Physicians Service Financial) so I actually had a "replacements" table with 2 fields called: ReplaceWhat and ReplaceWith. I would loop through the "replacements" table on each CustomerName field and used the instr function like iif(instr([CustomerName])," & ReplaceWhat & ") (not sure on the exact syntax though) then ...."call function to replace ReplaceWhat with ReplaceWith...." There was probably a much easier way to do this than what I described. I just liked using the technique of having a "replacement" type table where I could put new values in it and then just run through the function again without having to do a lot of re-coding for each new wording discovered. Also, since it was based on files being imported with all kinds of different situations, it was worth constructing (hope I didn't confuse you more on it - I was trying to give you a technique to use verses the actual code.)
    Last edited by pkstormy; 08-03-06 at 12:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you come across any code the attached function can't parse, please let me know.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    If you come across any code the attached function can't parse, please let me know.
    Interesting... I've seen your T-SQL version of that before. What's a SQL guy doing with a VBA translation of a T-SQL script if you don't mind me asking? Was it to avoid translating into PL\SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I was an Access/Excel guy before I was a SQL guy.
    I started with MS Access 2.0 more than ten years ago. The FormatName function, along with a few other of my favorite functions, actually started as VBA code to deal with uncontrolled data entered into spreadsheets by sales people that I then had to fit into an MS Access data warehouse.
    So actually, many of my favorite algorithms began as VBA which was only later translated into SQL.
    I still do a fair amount of MS Access development for clients.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Nice, blindman. I don't have a need for it right now, but it's going into the library. Thanks for posting it.
    Paul

  9. #9
    Join Date
    Aug 2006
    Posts
    3
    Wow. Thanks so much. We've been having some server issues, so I don't know when I'll get to try it out, but I'm sure it will help loads.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    code

    Impressive Blindman. I went through the algorithm and sure could have used it in certain situations.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks for the kudos. I have a SQL version as well. All I ask is that you let me know of any names that if fails to parse, so that I can make it as comprehensive as possible.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Never come across any personally but to account for archaic UK class system there are:
    Dukes, Duchesses, Barons, Baronesses, MBEs, OBEs, Lords, Ladies, Lairds, Earls, Counts, Countesses, Viscounts and Viscountess. I see you got Sirs.

    That's assuming you don't have to parse any Kings, Queens, Princes or Princesses. Or Queen Mothers.

    Merely in the interests of comprehensiveness
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nice toy, blindman
    safely in the library for 'next time'

    you have Mme, Mlle, but not Monsieur or it's single character abbreviation: M.

    izy
    currently using SS 2008R2

Posting Permissions

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