Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2005
    Posts
    7

    Question Unanswered: Update a column based on values from different columns that don't exactly match

    Hi

    This is my very first post and I'm hoping you can help me with this problem I like to solve using MS Access 2003.

    Situation:
    I have two tables below:

    Table 1: (transaction table)
    ------------------
    Code | CodeDesc
    ------------------
    xxxx Managerial
    xxxx Chef
    xxxx Wife
    xxxx Cleaner

    Table 2: (reference table)
    --------------------------------
    OccupationCode | OccupationDesc
    --------------------------------
    1000 Manager
    1001 Housewife

    Question:
    In the given tables above, how can I update the Code column of Table 1 with the value of OccupationCode column in Table 2. (my problem especially is if the value in CodeDesc Table 1 does not equal with the value in OccupationDesc of Table 2.

    Is it possible at all to update xxxx based only part of the word containing the values contain i.e xxxx Managerial updated to 1000 Manager.

    Hope my questions makes sense at all. If not please let me know. Any suggestions or solutions are very much appreciated. Please help!

    Cheers!
    Last edited by zerogone; 11-25-08 at 18:20.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How many codes are there in table 1 to update? It might be quickest to update them manually?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and I suppose the next question should be why are there different codes?

    what you coudl try doing is create a table containing the codes from the transaction tableand an "Equivalent to" code from the Reference table.
    insert all the currentvalues fromt he transaction table using an insert based on a select distinct
    then manually go through and edit the "Equivalent to" codes

    run a query peridically/as required which returns any codes which have transaction code, but no associated reference code.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2005
    Posts
    7
    Thank you so much for all your input so far

    @StarTrekker

    The number of records to be updated with codes will vary from time to time (around 50 to 100 records every hour or so).

    @healdem
    My problem at the moment is that I have no control with what the users enter on the Occupation field from another tool (which is a free text field which also means that what they enter on the field will not exactly match from what I have on my reference table [Table 2]). That other tool by the way produces an xml file which I then extract (this part is already done on my part) but I have to clean up that data before processing it again into another system which requires these codes (i know that clean data should be coming from the source tool but unfortunately it will require some time for developers of that tool to make the changes so I have to try and work out this mess). I'm hoping that I can temporarily counter this problem with some solutions while I'm recommending changes to the system that started this mess.

    So basically what I'm saying is can I update a certain field based only on the slightly matching values i.e. can i do a like statement in my update that if the column in the table i'm updating contains the word Managerial and my reference table has Manager then I can update the Managerial column with the code of the Manager from my reference table.

    Sorry for the long post.

    Thank you.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Do not do this.


    Just don't.

    Your process will eventually make a bad decision and it will cause you great pain. It does not matter how great your matching algorithm is. It may take some time, but eventually it will happen.


    The best way around this is to develop a quick and easy way of updating the values to something correct, and have your ETL process kick out an exception report to be manually reconciled.

    Learn from my pain. Please.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed 100% with Teddy. Don't go there.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jun 2005
    Posts
    7

    Talking

    Thank you all very much for your thoughts on this one.

    Solution:
    I've finally solved this problem by cleaning up the data using a series of updates to the Code column of Table 1 based on the OccupationDesc.

    Example:

    UPDATE Table1 SET OccupationCode = '1000'
    WHERE Table1.OccupationDesc LIKE '%manager%'
    OR Table1.OccupationDesc LIKE '%mgmt%'
    OR Table1.OccupationDesc LIKE '%mngr%

    and so on...(for other occupation categories)

    This works for me now. Thanks again.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    still not a good idea.

    You might end up with someone putting in an erroneous management tag that doesn't actually make sense. What about if the description matches more than one of your terms? The "what if's" are impossible to anticipate in this scenario. Just don't do it!




    If you insist on going this route (which I strongly, STRONGLY recommend against), then you should consider storing your terms in a separate table and using a single update statement instead of a bunch of little bits in your WHERE clause.

    UPDATE Table1 SET OccupationCode = TermCode
    FROM TermDefs
    WHERE Table1.OccupationDesc LIKE "*" & TermDefs.OccupationTerm & "*"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jun 2005
    Posts
    7
    Thanks Teddy

    I forgot to mention that at the moment there really is no strict rule in the process when entering that Occupation Code (except that it's 4 digit) into the final system.

    As an example scenario, the user/operator can manually enter '0000' if he/she can't identify which code the occupation description falls into. So '0000' is used as a generic code for everything else.

    What I'm doing is basically cleanup as much as I can while it's still ok in the current process but I do agree with you totally that this is not the most efficient way of tackling this problem.

    I'll try your suggested solution below for the meantime. Also how do I structure TermDefs table? Can you please give me an example?

    Thanks.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's not a question of efficiency, it's a matter of integrity and fault tolerance.

    "Inefficient" just means it takes more steps than it needs to. What you're doing here could result in matches that are just plain wrong. To me, that's unacceptable, but you have defined your own threshold for fault tolerance.


    Anywho, TermDefs exists purely to assign a partial term to a specific OccupationCode. All you need is a column for the partial term you intend to match, and a column for the OccupationCode that you would like to assign should you hit a match:


    TermDefs
    ---------
    OccupationCode
    TermCode


    You may have three separate entries with "Manager", "Mgr" and "Mngr" all with the same TermCode of 1000.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Jun 2005
    Posts
    7
    That's great and as a bonus you've also corrected me the right english terms and what it means as well (good for me to improve my english).

    I really appreciate your help. Thanks so much.

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Elegant Teddy,... nicely done
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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