Decyphering fields based on another tables information

[Rjconrep]

I have two tables one table is called BuyerGuideCodes and the other is called BuyerGuideCodesDescriptions.

Below is a sample record of table BuyerGuideCodes:
ID:
1
COMPANY:
CJ Systems Aviation Group
CodeID:
SS, OP, AS, AL, AN, AO, BS, GA, HD, IT, RO, PS, TR, MK

The table BuyerGuideCodesDescriptions has a description for each value in the CodeID field for table BuyerGuideCodes.
Sample data from BuyerGuideCodesDescriptions:
ID CodeID CodeDescription
42 SS Air Medical Support Services

I need the field CodeID in BuyerGuideCodes to be broken out and decyphered based on the information from table BuyerGuideCodesDescriptions.
I have the following query written but I have to create muliple fields and then repeat the query to many times. There has got to be an easier way. HELP!
UPDATE BuyerGuideCodes SET CodeDescription14 = MID(CodeDescription14,1,INSTR(1,CodeDescription14,", ")), CodeDescription15 = MID(CodeDescription14,INSTR(1,CodeDescription14, ", ")+1)
WHERE INSTR(1,CodeDescription14,", ");

 

[traingamer]

You should not have comma-delimited data in a single field.
Your buyer's guide codes should have one entry per code id. Something like:
id compcode codeid
1 1 SS
2 1 OP

...
57 2 SS
...

Then a separate company table (if you don't already have one):
id name
1 CJ Systems Aviation Group
2 Other company name
...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Rjconrep]

Yes, I know but this is how I received the data. Any suggestions?

 

[traingamer]

I think I understand the starting data.
What are your expected results? Do you want another field with comma-delimited descriptions to match those in CodeId?
You could write a public function, pass the CodeID string, use the Split() function, lookup the answers and return a string (or a var) and write a memo field. ...or...


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Rjconrep]

I want a seperate field for each description.