Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using concatenation to translate values (Access 2k, Win 2k)

    Hello Again

    I'm trying to work out how to translate a string into an equivalent string.

    Here's the story so far. The company i work for are changing the product codes used to a newer. more flexible, version, however in the interim there is a need to take a new code and convert it back to an 'old' code. We have a table with all the new values in one field and, where applicable, the old values in another field. I've used the mid function to parse out the new codes into those sections that have an equivalent, but now, how do I get 5 fields to find their equivalent values in a table of around a thousand records and display the equivalents in individual fields in the same query record?

    Once I get over this 'simple' hurdle I can contatenate the query output and there will be the 'old' product code.

    I could do it with one query per field, then brig them all together in a 'combination' query, but I'm hoping there is a neater way of doing this with queries.

    Thanks

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using concatenation to translate values (Access 2k, Win 2k)

    I'm sorry, I don't understand your description. If the new codes are in one column and the corresponding old codes in another column, it would seem to be a straight lookup, why do you need to parse? Please provide a more detailed description, and/or post an example.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using concatenation to translate values (Access 2k, Win 2k)

    Hans

    OK, knew I should have included an example.... sorry

    eg of new code: -VSIAL1AEF1CXA2H01

    equivalent old code: -IA025F1CXA1H1

    The codes are constructed from options, each character pair, or the odd individual, being the code for an option. It is the options I have in two columns (I really messed up explaining that bit) rather than the entire codes. So, by parsing out the parts of the new code that have an equivalent, in this case: -

    VS has no match
    IA= IA
    L1 has no match
    AE = 025
    F1 = F1
    CX = CX
    A2 = A1
    H01 = H1

    I can find the old option value in the translation table, having found the options values I can rebuild them into the old code using concatenation. Where I'm struggling is to work out how to match my 7 parsed fields to the 7 records that will allow me to rebuild the old code.

    I hope that makes more sense??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using concatenation to translate values (Access 2k, Win 2k)

    How do you decide whether a part consists of 2 characters or of 3 characters (or ...)?

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using concatenation to translate values (Access 2k, Win 2k)

    The new code uses the same number of characters regardless of the product, if there is no need for an option we populate that part of the code with null values. With the exception of the last three characters all options in the new code are two characters.

    The table we have with the translation values has four additional columns in it for the start and stop position of the two options. in my earlier example for instance IA would have a start position value of 3, stop position value of 4, in the new product code, while having a start position value of 1, stop position value of 2, for its equivalent value in the old code.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using concatenation to translate values (Access 2k, Win 2k)

    So you can create a query with each part in a separate field (column).
    Create a query based on this query. Add as many copies of the translation table as there are parts.
    You may want to give them aliases such as T1, T2 etc.
    Draw a line from the first part to the "newpart" field in the first copy of the translation table.
    Double click the line and specify that you want to return ALL records from the query containing the parsed parts.
    Repeat for each of the other parts.
    You can now create a calculated field of the form

    OldCode: [T1].[oldpart] & [T2].[oldpart] & ...

    where oldpart is the name of the field containing the old part.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using concatenation to translate values (Access 2k, Win 2k)

    Hans

    I'll give it a go, I just don't have your genius for this sort of thing.

    Thanks for the time and patience in getting the truth out of me!!

    Ian

Posting Permissions

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