Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: Normalize Name and Address Fields

    I've seen a number of 3rd party add-ons for Access to normalize name and address fields, but I would like to accomplish this without having to purchase one. I will ask this question in two parts, but they are related. I've been given a fixed width text file and have successfully created an import specification.

    1) What I need to do now is split the name field into first and last. The catch is that some of the names are companies, some are last and first names separated by a space and others are like "DOE BOB & SUE"

    2) I also need to separate the Address field which contains data like "Los Angeles CA92625-3345"

    Any suggestions? Thanks!
    BillS

  2. #2
    Join Date
    Mar 2002
    Location
    Cornwall, UK
    Posts
    18
    The first thing you will need to do is determine how you can identify whether the record is for a person(s) or a company. I assume from your post that a company record relates to just one company, so you would not need to parse the name, but a personal record may relate to more than one person, so the combined names (given and family) need to be split before parsing them. The problem, as I guess you are finding, is that there are so many possibles. For example:

    BUSINESS AND FINANCE MANAGEMENT
    PHILIP AND MIJOU AVERY
    BUSINESS FINANCE AND MANAGEMENT
    PHILIP AVERY AND MIJOU

    How do you differentiate?

    What you need is consistency. I always do this type of data cleansing in Excel before importing it to Access, because that is what it's good at. I run code to highlight the obvious (e.g. a limited company) in a particular colour. What is uncoloured has to be dealt with, but hopefully a lot will be eliminated. I then look for clues that identify different types, write code to deal with them and so on. At the end of the process I am left with 'stuff' that needs to be changed by hand to be consistent with rules that can be handled by code.

    The one thing I've learnt is that people will always come up with new and illogical ways of entering data, so I've never seen an off-the-shelf package that works particularly well. Go back to the example - you know which is the business name but how can you write code to distinguish between them?

    Sorry, it's just hard work - can you define a set of data entry rules and make whoever is responsible clean the data using them?

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I think Philip has pretty much outlined the problem, and "Sorry, it's just hard work" pretty much points to the answer. With all the many variables involved here, I don't see any real possibility of cleaning up the data using code. Setting up a set of data entry rules and having people go over the existing data and bringing it into compliance with these rules is going to be the only viable answer. I've been involved in this process before when one of the mega-banks bought out a smaller bank and had to bring the smaller bank's data into compliance with their own software before importing its customers' data. The important thing is to think long and hard before putting down your data entry rules; you don't want to get halfway thru the process and realise you've forgotten something! Good luck!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Thanks, that's what I was worried about. Unfortunately the data I'm working with comes from a municipality and there's no way they are going to change their data for me.
    Last edited by BillSinc; 05-15-06 at 12:04.
    BillS

Posting Permissions

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