Query to extract data help please

[Terry DeJournett]

I have one field in a table that contains 5 different values, each seperated
by a ",". (Example: Value1,Val2,V3,Value4,Valu5). Each value may be of
different lenghts (6 characters, 4 characters, etc.) The only common factor
is that each if seperated by a comma (,).

Would anyone know how to run a query to extract the first value and put in
into a new table in field1, then put the next value in field 2, etc. In
orther words to take the entire value of this field and put in into seperate
fields.

Thanks in advance for any suggestions.

T

 

[Guest]

Today is your lucky day! Since the data is seperated with commas, Access can
handle it with a little work.

First of all does that table have a primary key? If so great. If not create
an autonumber field in the table and make it the primary key.

Next create a query with the primary key field and the field in question.
However you need to join these two fields together with a comma. Something
like this:

SELECT [CommaPK] & "," & [TheCommasField] AS CSVed
FROM tblCommas;

Run the query and make sure that it looks OK. After running it, export the
query results as a text file. When you get to the Export Text Wizard dialog
box about Text Qualifier select None.

After exporting the query to a text file, import it back in using Get
External Data. This will create a new table which you can join to the
existing table using the primary key field.

The above is something good for a one-time cleanup of data. If this is
something that you have to do frequently, there may be other ways.