So the first column contains a unique account number, and the 2nd column
contains concatenated codes. In a relational database, you would want these
as many *records* in a related table, instead of concatenated values in one
very wide memo field.
1. Create a table with 2 columns:
AccountID Number (if it's a real number), or Text (if non-numeric.)
TheCode Text (size = 3 char.)
2. Select both the field together, and make the combinbation of the 2 the
primary key.
3. Save the table as (say) AccountCode.
4. Write some code to populate the table based on the records from the
original one. This will OpenRecordset() the source table, and OpenRecorset()
the target table. Loop through the records of the source table, using
Split() to parse the concatenated codes into an array. Then loop through the
array, and for each member AddNew to the Target table (and Update.)
If the average record has 100 of those 3-letter codes, your taget table will
now have 6 million rows. But it will be much more queryable.
After you've done this, you will want to create another table contains a
record for each of the valid codes. You can create it with a Make Table
query, using DISTINCT on TheCode. Then open the table in design view, mark
the field as primary key, and add any other fields (e.g. explanation of the
code.)
You probably also want a real Account table (with the unique AccountID, and
fields indicating what the account is for.)
Then don't forget to create relationships between the 3 tables.
If the concept of connecting up the 3 tables like that is new, here's
another example:
http://allenbrowne.com/casu-06.html