Results 1 to 14 of 14
  1. #1
    taholmes160 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Lester, Ohio
    Posts
    65

    Changing values in a record

    Hi Guys:



    I have a big database that i am working on. For this particular part of the project, I would like to repair a bad abbreviation that was used in this section of the database

    CommodityID Commodity
    5337 Elec eqt
    5338 Elec gen systems
    5339 elec goods
    5340 Elec HH appl: refrig
    5341 Elec lamps
    5342 Elec lanterns
    5343 Elec machy
    5344 Elec meters
    5345 Elec motor dlr
    5346 Elec motor parts
    5347 Elec motors
    5348 Elec motors fract HP
    5349 Elec panels
    5350 Elec plating supp dlr
    5351 Elec power plt
    5352 Elec prod
    5353 elec prod mfr
    5354 Elec prods plt
    5355 Elec prods whse
    5356 Elec repairs & distr
    5357 Elec resistors
    5358 Elec rheostats
    5359 Elec solenoids
    5360 Elec supp
    5361 Elec supp & appl
    5362 Elec supp dlr
    5363 Elec supp plt
    5364 Elec supplies
    5365 Elec supplies (distributor)
    5366 Elec supplies dlr
    5367 Elec switches
    5368 Elec transf
    5369 Elec transformers
    5370 Elec utility
    5371 Elec wire
    5372 Elec. Appliances
    5373 Electr comps
    5374 electr prod
    5375 Electric Appliances

    as you can see the abbreviation ELEC is used in place of Electrical -- Id like to be able to fix this as a batch thing. Is it possible, or do I have to go through and edit each of those by hand?

    Thanks
    TIM

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,965
    Try an update query with the Replace() function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,877
    answer removed
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    426
    If you use the replace function be precise.

    if you have:
    Elec utility
    Elec. Appliances
    Electric Appliances
    Electr comps

    and do a replace like Replace("YourField","Elec","Electric") you wil wind up with
    Electric utility
    Electric. Appliances
    Electrictric Appliances
    Electrictr comps

    I would first do a replace like
    Replace("YourField","Elec ","Electric") Note the space after "Elec "
    this will eliminate the majority of them and then re-evaluate.

  5. #5
    Eugene-LS's Avatar
    Eugene-LS is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2018
    Location
    Murmansk
    Posts
    16
    UPDATE [Your table name] SET Commodity = "Electrical " & Mid([Commodity],InStr([Commodity]," ")+1) WHERE Commodity Like "Elec*";

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,713
    Tim,

    Is this a home grown classification system or is it from some general commodity system?
    Hopefully any usage of these codes in your application(s) is using the CommodityID and not the Commodity in related tables.

    I would start by looking at the distinct name parts you have, then decide on the most common/useful abbreviation for each name part. And as Moke says be careful in trying to do too much in one step. You might even do some select/Read Only queries to see what would be selected (and modified) before doing any direct updates/replace etc.

    I thought these might be FSC codes but I couldn't find them there?? And not SIC codes either??
    Always more compatible for future if you can align with a recognized, maintained coding system.

    Good luck.

  7. #7
    taholmes160 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Lester, Ohio
    Posts
    65
    Hi Guys / Ladies

    Thanks for all the help and suggestions, The database is a gigantic one which is originally in a text file (of all terrible things) and is in very very poor condition. It has been contributed to by many people over the years. It is a list of shippers and receivers of various commodities for model railroaders to use when developing loads for their trains to carry. It has had NO data normalization at all -- there have been multiple instances of multiple spellings of the same commodity (ie auto, automobile, atoes, Auto, Autos, Automobile, Car, Cars...) I am trying to get all identical items pulled together, and updating companies as I go. Ive winnowed the list down from over 10,000 commodities to around 7000, and I have a good deal more to go. After that the next step will be figuring out what type of industry each commodity belongs to and getting that all in a separate table as well.

    TIM

  8. #8
    Join Date
    Apr 2017
    Posts
    1,073
    Sequently run queries
    Code:
    UPDATE YourTable
    SET Comodity = "Electirical " & MID(Commodity, 6,250)
    WHERE LCASE(LEFT(Commodity,5)) = "elec "
    
    UPDATE YourTable
    SET Comodity = "Electirical " & MID(Commodity, 7,250)
    WHERE LCASE(LEFT(Commodity,6)) = "elec. "
    
    UPDATE YourTable
    SET Comodity = "Electirical " & MID(Commodity, 8,250)
    WHERE LCASE(LEFT(Commodity,7)) = "electr "
    
    UPDATE YourTable
    SET Comodity = "Electirical " & MID(Commodity, 10,250)
    WHERE LCASE(LEFT(Commodity,9)) = "electric "

  9. #9
    taholmes160 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Lester, Ohio
    Posts
    65
    HI Guys:

    Thanks a bunch for the info, being that I dont particularly speak SQL all that much, could you please parse out what those are doing, I know replacing the words, but im not sure how they work, and I'd like to learn

    Thanks a bunch

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,713
    For info here are the 48 unique text strings in field Commodity. Are you dealing only with variations in Elect/Electr... or cleaning up all texts?
    Code:
    &
    (distributor)
    appl
    appl:
    Appliances
    comps
    distr
    dlr
    elec
    Elec
    Elec.
    electr
    Electr
    Electric
    eqt
    fract
    gen
    goods
    HH
    HP
    lamps
    lanterns
    machy
    meters
    mfr
    motor
    motors
    panels
    parts
    plating
    plt
    power
    prod
    prods
    refrig
    repairs
    resistors
    rheostats
    solenoids
    supp
    supplies
    switches
    systems
    transf
    transformers
    utility
    whse
    wire
    
    Unique texts in ArrayList :48
    Last edited by orange; 12-10-2019 at 11:37 AM. Reason: provide sorted list

  11. #11
    taholmes160 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Lester, Ohio
    Posts
    65
    Hi Orange

    The whole thing

    TIM

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,713
    Tim,

    Do you have a list of questions/issues that you need help with?
    Since it's the whole thing, I suggest you start with the 30,000 ft level description of the requirement and identify priorities. Then focus on those priorities while keeping the big picture in mind as you proceed.
    We're here to help, but as always, you know the environment and requirement better than any reader, so putting issues into context will help get more focused responses. Clarity is key.

    Good luck.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    I know this goes without saying, but I'm going to say it: You should be practicing on a COPY of the dB. (You are,.... aren't you???)

    Remember the 3 rules of computing:

    1) Back up
    .
    .
    .
    .
    2) Back up
    .
    .
    .
    .
    .
    and

    3) BACK UP!!







    Good luck with your project.......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    taholmes160 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Lester, Ohio
    Posts
    65
    LOL -- ya, you bet I do have backups

    TIM

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-23-2019, 12:44 PM
  2. Changing values based on a field
    By Podder in forum Access
    Replies: 3
    Last Post: 09-01-2018, 12:37 PM
  3. Changing Record values with VB
    By RevBlueJeans in forum Access
    Replies: 3
    Last Post: 03-20-2015, 08:32 AM
  4. Handling Duplicates and Changing Values in VBA
    By pjgoodison in forum Programming
    Replies: 5
    Last Post: 04-28-2013, 01:34 PM
  5. Replies: 3
    Last Post: 08-11-2009, 10:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums