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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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??

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote