Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    41

    Unanswered: How would you make a field with many inconsistent values consistent?

    I have a table with about 200 records.

    This table has a field that has is called "Packaging."

    Over time, manual data entry has caused many differences in the *text* of the same package, for example...

    Some records might have

    25LB Paper
    25 LB paper
    25Lb Paper

    I want to change all the records so they are consistent to a single value (25 LB Paper)

    At first, I tried running a query to find the DISTINCT packagins, then finding and replacing each one, but this seems inefficient.

    Is there a better way to do this?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Data in a column is said to come from a "domain". This is just a fancy way of saying what the allowable values are. To ensure consistency you would not allow people to enter text freely, you would specify a list of allowable values and restrict people to those values only. This is something you should consider moving forward, otherwise you will do this job many, many times. We can help you sort out your domain (for example, I think this column could be better split into 2 or 3 more columns) but we'd need to thrash that out.

    As far as updating goes, I would carry on as you are. There are only 200 rows. If there were 2 million, or even 2 thousand, it would be worth coming up with something more fancy.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2009
    Posts
    41
    Thanks pootle.

    Limiting the values to a list is something I'm going to be doing once I combine all of these inconsistencies.

    Fortunately, the list isn't that long, and the distinct values narrows down to about 30.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree with pootle, at 200 records it'll take less time to simply manually change the data than it will to try to develop a hack to "automatically" do it. As with trying to create a hack for parsing addresses or whole names into the various components, the possibilities are almost endless and covering all of them will require a huge amount of work. Then use a Listbox or Combobox for your users to choose from.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    May 2009
    Posts
    41
    Quote Originally Posted by pootle flump
    We can help you sort out your domain (for example, I think this column could be better split into 2 or 3 more columns) but we'd need to thrash that out.
    After closer inspection, I've found that this Packaging(text) field is already separated into 2 other columns that hold the *weight value* and the *unit value*

    i.e.
    This table has the following fields (w/ example after):
    Packaging (text): ex. "25 LB Paper"
    Weight (number): ex. 25
    Unit (text): ex. "LB"

    I'm guessing the text was used only for display, and the others for calculation purposes.

    Is the proper way to handle this to delete the Packaging text, and run a query to concatenate the weight and the unit, if needed?

    Thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You have a normalisation problem now then.
    Those component columns are what I had in mind. All you need now is a "packaging_material" column, or similar. Then your text would be exactly as you say, a concatenation:
    Code:
    SELECT weight & " " & unit & " " & packaging_material AS packaging_text
    As such, you don't need to correct all the packaging text columns, just add this new one, enter the correct material and delete the packaging text column (take a copy of your database before you do of course).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    You have a normalisation problem now then.
    now... then... make up your mind

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2009
    Posts
    41
    Quote Originally Posted by pootle flump
    You have a normalisation problem now then.
    Those component columns are what I had in mind. All you need now is a "packaging_material" column, or similar. Then your text would be exactly as you say, a concatenation:
    Code:
    SELECT weight & " " & unit & " " & packaging_material AS packaging_text
    As such, you don't need to correct all the packaging text columns, just add this new one, enter the correct material and delete the packaging text column (take a copy of your database before you do of course).
    Thanks, pootle... worked like a charm.

Posting Permissions

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